Extracting string from between commas

[Kasper Madsen]

Hello everyone!

I have been using loads of good advice from this forum by reading about other people's Excel problems - and now I've arrived at a problem I can't seem to find a previous solution to:

I have a column of data, with each cell containing from 0 to 6 strings, separated by ", " (a comma and a space).

I need to extract each of the words between the commas and put one each into the following 6 columns in my spreadsheet (basically the same way as the 'Text to Columns'-button, but this NEEDS to be automated).

I have attempted something along the lines of inserting this formula in each of the following columns:

Column 1
=IF.ERROR(MID(A2,1,(FIND(",",A2,1)-1)),"")

Column 2
=IF.ERROR(MID(A2;FIND(",";A2;FIND(",";A2;1))+2;FIND(",";A2)-1);"")

...


But obviously this only works if the strings are all the same length (which they are not).

Can anyone help me arrive at a solution? Any assistance will be greatly appreciated!


Sample data:

A2
Svanemærke, Astma, Øko-Tex

A3
Svanemærke

A4
Svanemærke, Øko-Tex

 

[Gerald Higgins]

Sorry Kasper, I never tried those formulas on longer text strings .
I see what you mean now.
Try these.
In B2

=IFERROR(LEFT(A2,FIND(",",A2,1)-1),A2)

and in C2

=IFERROR(MID(A2,FIND(",",A2,1)+2,FIND(",",A2,FIND(",",A2,1)+1)-FIND(",",A2,1)-2),"")

You should be able to adapt the C2 formula for D2 etc, but it may get messy.

 

[energman58]

This is completely general and not too horrific

In column D and copied right


=IFERROR(MID($A2,IFERROR(SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-3)&CHAR(127))+2,1),({11})-IFERROR((SEARCH("",SUBSTITUTE($A2,", ",CHAR(127),{0})&CHAR(127)))-2,1)),"")

EDIt - no it isnt it only works for the first one and I cant tell why!

Suggest using Gerald's Formulation for the first occurrence and my previous formula for numbers 2 to n

 

[Kasper Madsen]

Here is a formula solution

You can obviously do the first one and this formula which is designed to sit in column d and copied to the right will find the second, third etc. if they exist

If you dont want the second ocurrance to be returned in D you need to adjust the Column()-3 etc terms appropriately (if it is C you want you reduce 3 to 2 etc)


=IFERROR(MID($A2,SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-3)&CHAR(127))+2,(SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-2)&CHAR(127)))-(SEARCH(CHAR(127),SUBSTITUTE($A2,", ",CHAR(127),COLUMN()-3)&CHAR(127)))-2),"")


I am sure there is a clever way of making it completely general so it gets teh first one too but I cant be bothered to work it out!

This is brilliant - Thanks a million! It really works wonders when I test it using the example above - however I am running into one problem:

My actual data is located in Column O (not in A as in the example), but when I use the formula in Column R (changing all references from 'A2' to 'O2') with my data in Column O, it doesn't seem to work, but I can't figure out why!

 

[energman58]

It is the "column terms" in the formula - they simply count the "nth" time the search string occurs so the first time it returns the second word, next time the third etc.

So change A2 to O2 etc. (as you have done) but you need to adjust the column references too:

Column() will return 18 in R instead of 4 in D

so you need to change COLUMN()-3 in the formula I wrote for D to COLUMN()-17 in R and change Column()-2 to Column()-16 and so on

Then copy it to the right and it should work fine

 

[Kasper Madsen]

Perfect! That was exactly what I was missing. It seems to work flawlessly now..!

Many thanks to all contributors - I am extremely grateful for the help!

ray:

 

[Peter_SSs]

I'm a bit late, but a word of warning about the formula suggested in post #10. The use of the COLUMN() function in this formula is risky. If for some reason a column is subsequently inserted to the left of the formulas (eg a new column A is added for labels) then the formulas will return incorrect results.

In any case, what about this simpler formula that can be used for all cells? Copy across and down.

It does rely on your original assertion

each cell containing from 0 to 6 strings, separated by ", " (a comma and a space).

Excel Workbook
	A	B	C	D	E	F	G
2	text1, longtext2, text3	text1	longtext2	text3
3	a, b, c, d, e, f	a	b	c	d	e	f
4
5	a, big red, apple, was eaten, yesterday	a	big red	apple	was eaten	yesterday
Split Text

 

[Kasper Madsen]

I'm a bit late, but a word of warning about the formula suggested in post #10. The use of the COLUMN() function in this formula is risky. If for some reason a column is subsequently inserted to the left of the formulas (eg a new column A is added for labels) then the formulas will return incorrect results.

In any case, what about this simpler formula that can be used for all cells? Copy across and down.

It does rely on your original assertion

Excel Workbook
	A	B	C	D	E	F	G
2	text1, longtext2, text3	text1	longtext2	text3
3	a, b, c, d, e, f	a	b	c	d	e	f
4
5	a, big red, apple, was eaten, yesterday	a	big red	apple	was eaten	yesterday
Split Text

Wow - it gets even better. I must say I'm impressed with the help provided on here. Thanks a lot for the assistence, this last formula is exceptional!

 

[Marcol]

Why don't you just use Text to Columns? ...

The data is separated with ", " (Comma space) so use this to "Trim" the result.
In B2

=SUBSTITUTE(A2,", ",",")

Drag/Fill Down

Copy the result then Paste > Paste Special > Values
Then Text to Columns > Delimited > Next
Select Comma > Next
Destination:= $C$2 > Finish.

This is quicker to do than to explain!

 

[Peter_SSs]

Why don't you just use Text to Columns? ...

I think because the OP wanted this to be automated. The text to be split is the result of a VLOOKUP formula. If the formula results change, the Text to Columns results will not automatically update whereas the formula solution will.

 

[Kasper Madsen]

I think because the OP wanted this to be automated. The text to be split is the result of a VLOOKUP formula. If the formula results change, the Text to Columns results will not automatically update whereas the formula solution will.

Yes, exactly! And in addition to this, the document I am creating is to be used by product specialists with no Excel-experience whatsoever. They need to input Product-IDs into column A of the first sheet and everything else in the document has to be filled out automatically

Besides, does the Text to Columns-button even work on data from a VLOOKUP formula in the first place?