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

 

[Peter_SSs]

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

No it doesn't. You would have to do a copy/paste values at some stage (as Marcol described). And it is a one-off process - it does not update if the original data changes by any means (formula or manual entry).

 

[Marcol]

I missed the bit about the VLOOKUP, but it would still work if you used the SUBSTITUE() formula with Copy paste special values as I suggested.

However as you need a fully automated solution you'll find Peters' solution hard to beat.

 

[Peter_SSs]

The formula could be simplified further by adding a new row 1, populating it manually with the numbers shown (or using the formula shown in C1 copied across) then hiding row 1. The B1 formula is now simpler.

Excel Workbook
	A	B	C	D	E	F	G
1		1	101	201	301	401	501
2	Original Data	Txt 1	Txt 2	Txt 3	Txt 4	Txt 5	Txt 6
3	text1, longtext2, text3	text1	longtext2	text3
4	a, b, c, d, e, f	a	b	c	d	e	f
5
6	a, big red, apple, was eaten, yesterday	a	big red	apple	was eaten	yesterday
Split Text (2)

 

[Joyner]

Hello, I know this is an old thread, but in searching for an answer this one came up, so I thought I would add to it for anyone else.

Peter’s formula is perfect for extracting the text between commas, it is an elegant and succinct formula. I did take out a space after the comma for the replace part so it would work for any text string, space or no space after the comma. I also modified it so the formula could be copied down rows also. Because of the TRIM, this formula will remove extra spaces inside the text and before and after – if you need to retain the spaces see the formula below. Here are the two, first to copy across columns, second to copy down rows:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",100)),(COLUMNS($B1:B1)-1)*100+1,100))

=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",100)),(ROWS(A$1:A1)-1)*100+1,100))

The formula from energman58 retains the spaces in the text (before comma, after comma, and within text, so everything between the commas), but was only working for the occurrences after the first text and comma, and Gerald Higgins had the part for the first test string. I combined the two to work for all occurrences, and modified it to work in any column as Peter warns in his post. There may be better ways to do this, but I just modified these. Again I created one to copy across columns and one to copy down rows, below respectively:

=IFERROR(MID($A$1,SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),COLUMNS($A$1:A1)-1)&CHAR(127))+2,(SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),COLUMNS($A$1:A1))&CHAR(127)))-(SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),COLUMNS($A$1:A1)-1)&CHAR(127)))-2),IFERROR(LEFT(A$1,FIND(",",$A$1,1)-1),$A1))

=IFERROR(MID($A$1,SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),ROWS($A$1:A1)-1)&CHAR(127))+2,(SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),ROWS($A$1:A1))&CHAR(127)))-(SEARCH(CHAR(127),SUBSTITUTE($A$1,",",CHAR(127),ROWS($A$1:A1)-1)&CHAR(127)))-2),IFERROR(LEFT($A1,FIND(",",$A$1,1)-1),$A1))

 

[nicholaspars]

I have a similar issue. I am attempting to extract the number before the last comma in a string.

I have so far come up with =RIGHT(D8,(FIND(",",D8,1)+2)) which returns 2.9800,548961. My issue is that I only want to extract the first four characters i.e. 2.98

The sample string is

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]2017-11-07,3.0100,3.0400,2.9700,2.9800,548961

Any help appreciated!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[pkdeimos]

Hi,

Can you try this:

=ROUND(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(D8),",",REPT(" ",50)),100))," ",REPT(" ",100)),100)),2)

Hope this helps.

Regards.

 

[nicholaspars]

Hi,

Can you try this:

=ROUND(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(D8),",",REPT(" ",50)),100))," ",REPT(" ",100)),100)),2)

Hope this helps.

Regards.

Works perfectly! Thank you so much for the quick response.

 

[Peter_SSs]

Works perfectly!

Would this shorter version also work for you?

=ROUND(LEFT(RIGHT(SUBSTITUTE(D8,",",REPT(" ",50)),100),50),2)

.. or if it actually 4 characters that you want as stated in your question, it may be

=LEFT(TRIM(RIGHT(SUBSTITUTE(D8,",",REPT(" ",50)),100)),4)+0

 

[nicholaspars]

Would this shorter version also work for you?

=ROUND(LEFT(RIGHT(SUBSTITUTE(D8,",",REPT(" ",50)),100),50),2)

.. or if it actually 4 characters that you want as stated in your question, it may be

=LEFT(TRIM(RIGHT(SUBSTITUTE(D8,",",REPT(" ",50)),100)),4)+0

Thanks Peter

Your 1st version is ideal. I don't think I was clear in my original request in that I wanted the output to 2 decimal places rather than 4 characters as stated, which is what your second formula obtains. So thanks, yes, your 1st shorter version does the trick returning the number to the left of the last comma to 2 decimal places.

Great forum you have here.

 

[Peter_SSs]

Thanks Peter

Your 1st version is ideal. I don't think I was clear in my original request in that I wanted the output to 2 decimal places rather than 4 characters as stated, which is what your second formula obtains. So thanks, yes, your 1st shorter version does the trick returning the number to the left of the last comma to 2 decimal places.

Great forum you have here.

You are welcome. Thank you for your feedback and we'll see you next time.