Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Extracting text



I'm trying to extract a text inside a cell between two commas no matter what the length of the characters, I think I'm using a wrong formula but that's all I get. Any help with that? 

Attached example, I highlighted the values that need to be extracted 



Selected Answer

Hi Jana,

This is the formula that will do the job.

=SUBSTITUTE(LEFT(MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100),FIND(CHAR(160),SUBSTITUTE(MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100),",",CHAR(160),2))-1),","," AND ")

It is based on the SUBSTITUE() function which allows you to specify the nth occurrence of a character to be changed. So, in  FIND(CHAR(160),SUBSTITUTE(C2,",",CHAR(160),2) the second comma is first converted to CHAR(160) and then "found" by the FIND() function. Remember to read the formula from inside to the outer. CHAR(16) is just any character which doesn't normally occur in the text. Replace with with another, if it does. 

The MID() being given a length of 100 characters is immaterial and intentionally large because the function ignores the instruction to retrieve characters which aren't there.

With that said, this is the way to build the formula.

[F2] =MID($C2,FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),2))+1,100)
[G2] =LEFT($F2,FIND(CHAR(160),SUBSTITUTE($F2,",",CHAR(160),2))-1)
[H2] =SUBSTITUTE($G2,","," AND ")

To assemble the formula, replace the two occurrences of $F2 in G2 with the full formula in F2 (without the = sign). Then repeat the action by replacing $G2 in H2 with what is now in G2.



Wow, that's awesome! It works perfectly, but I couldn't break it down for two parts let say if I want to extract only one value how it should look like?
Thank you
Jana86 (rep: 6) Dec 4, '18 at 8:31 am
The formula looks for the 2nd comma and cuts off everything before that. Then it looks for another second comma in the remaining string and truncates all that follows. If you wanted only the first item you would truncate the tail at the first comma. If it's the second item you want you would cut the leader at the third comma instead of the second. You can make these adjustments by tweaking the SUBSTITUTE() functions in F2 and G2 (in my above answer) respectively. CHAR(160),2 specifies the 2nd comma in each case. Change the number to either 1 or 3.
Variatus (rep: 4879) Dec 4, '18 at 8:47 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login