Break apart cell by commas

[dcamos]

I am trying to separate a cell into different cells based on the comma locations in the first cell. I am trying to do it with mid(find()) and just nesting more finds into the function but that is complicated. Is there an easier way to do this. This isn't a column but something i'd like to do automatically so i am trying to stay away from text to columns.

Thanks,

 

[Robert Mika]

Can you provide an example?

 

[dcamos]

The cell could have in it red, blue, yellow, green, happy birthday
and I want to put red in one cell, blue in another, yellow in another, green, then ...

 

[mikerickson]

Put this in a cell and drag right
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&A1",,", ",", REPT(255," "),COLUMN(A999)), ",",REPT(255," "),COLUMN(B999)),255,255))

 

[dcamos]

Mike,
I a m getting an error in the Substitute(","&A1" I think i needed to put a & after the 1, but it still does not work.

 

[mikerickson]

This should work,
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&$A$1&",", ",", REPT(" ",255),COLUMN(B999)), ",", REPT(" ",255),COLUMN(A999)), 255, 255))

 

[dcamos]

Works perfectly, thank you. Could you please explain to me why it works, so I can learn.

Thanks,

 

[nemish]

Can I ask for similar formula to break apart a cell by spaces?

So for example

A1
First Last

B1
First

C1
Last

Thanks

 

[mikerickson]

First, put a comma in front of the string and after it ","&A1&","

In that string, replace the n+1'th comma with 255 spaces SUBSTITUTE(","&A1&",", ",", REPT(" ",255), n+1)

In that string, replace the n'th comma with 255 spaces SUBSTITUTE(SUBSTITUTE(...), ",", REPT(" ",255), n)

That gives us a short string, followed by 255 spaces, followed by the item of interest, followed by 255 spaces followed another short string.

MID(SUBSTITUTE(SUBSTITUTE(...)...), 255 , 255) will extract the item of interest with some surrounding spaces.

TRIM(MID(...)) removes those spaces.

The only thing left is to input the n+1 and n values in the SUBSTITUTE's. This is done with the COLUMN(B999) and COLUMN(A999), both of which increase as they are dragged right.

 

[Robert Mika]

B1:=trim(left(a1,find(" ",a1)))
c1:=trim(substitute(a1,b1,""))