Extract string between delimiters

Greetings,

I'm looking for a formula that will take text out between commas. I'm familiar with the left and right formulas to return things at either end, but I'm not sure how to proceed with this one. I could use the text to column, but I'm hoping to have a formula to use instead.

expected result:
Source ColA ColB ColC ColD ColE
car car
car, tree car tree
tree, car, orange, umbrella tree car orange umbrella


I'm open to suggestions. Each of these items has a $value. The end result is that I'm able to state how much each item contribute to that rows' cost. Groupby would be useful here

I am not clear on what you are starting with and what the desired outcomes should be.

It would help if you would upload an example Excel workbook. Please do not post pictures.

Many of us cannot download / read these and none of us want to retype data. Hand type enough data to get

the idea across and the layout you envision with expected results. Include any additional details in the workbook. Desensitize the data.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done"

Originally Posted by soberguy

Mid is pretty fancy when combined with find.

Find(",",A1)

That will find the first comma in cell A1. So if you want the second comma as well, just set the first comma as the starting point for a find.

Find(",",A1,find(",",A1))

Make sense?

Then you can extract everything between those two points with mid.

Mid(A1,find(",",A1),Len(A1)-find(",",A1,find(",",A1)))

Hammered out on my phone, so hopefully it's close

Thanks I will give this a shot

Originally Posted by Tony Valko

Try this...

Data Range

	A	B	C	D	E	F
1
2	car	car
3	car, tree	car	tree
4	tree, car, orange, umbrella	tree	car	orange	umbrella
5	------	------	------	------	------	------

This formula entered in B2:

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),COLUMNS($B2:B2)*100,100))

Copy down to B4 then across until you get a column full of blanks.

This works. Can you explain why trim?

Also thanks Fdibbins for looking at it differently.

Originally Posted by Tony Valko

The SUBSTITUTE function (virtually) replaces the commas with 100 space characters:

The underscores represent the space characters.

__________car__________

So, we need the TRIM function to remove those space characters for our final result:

TRIM("__________car__________") = car

That's a very elegant solution. Thanks for explaining.