mid/instr/fin

[briank]

I have cells that contain multiple bits of data seperated
by a comma. For example: =a1(name, address, city, state,
postal). I would like to create different cells that
breaks out each bit of data. For example a2=name(a1) and
so forth. Is there a command(s) that will allow for this
flexibility?

 

[AndyB]

briank

Try Data | Text to columns.

Andy.

 

[J.E. McGimpsey]

one way:

Select your cell(s) and choose Data/Text to Columns. Click on the
Delimited radio button, then Next. check the comma checkbox, then
Finish.


If you want to do it via formula, here's one way:

name: =TRIM(LEFT(A1,FIND(",",A1)-1))

address: =TRIM(MID(LEFT(A1,FIND("#",SUBSTITUTE(A1,",","#",2))-1),
FIND(",",A1)+1,255))

city: =TRIM(MID(LEFT(A1,FIND("#",SUBSTITUTE(A1,",","#",3))-1),
FIND("$",SUBSTITUTE(A1,",","$",2))+1,255))

state: =TRIM(MID(LEFT(A1,FIND("#",SUBSTITUTE(A1,",","#",4))-1),
FIND("$",SUBSTITUTE(A1,",","$",3))+1,255))

postal: =TRIM(MID(A1,FIND("#",SUBSTITUTE(A1,",","#",4))+1,255))