comma delimited formula

[steve_stiller]

Hi,

Does anyone know if there is a formula built into excel that can take one cell and split it in half when a comma occurs.

a1="bob, smith"
a2="bob"
a3="smith"

And if one doesn't exist what would a good macro be to do this?

Thanks alot,

Steve

 

[Jonmo1]

Data - Text to Columns.
Select Deliminated - next
Check "Comma" - Finish


or if it must be formula,

A2 =LEFT(A1,FIND(",",A1)-1)
A3 =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

 

[brian.wethington]

highlight the column you need to do this to
select the data menu
select text to columns
choose delimited
Finish

 

[Norie]

Steve

There are both formulas and code that will do that.

But how about Data>Text to columns... using comma as a delimiter?

 

[steve_stiller]

Thanks everyone for your help. Both the formula and the Excel feature worked perfectly. Awesome.

 

[Bize32]

Worked for me Re: comma delimited formula

Hi Guys Thanks for a easier way for my line of work i made a batch File that copies my files from a Temporary location.

so i had "Templates" Ready

these get copied to my new location via the batch file.

I receive Files containing " | / Pipe"

in Column "A" i have
=LEFT(C1,FIND("|",C1)-1)

in Column "B" i have :
=TRIM(RIGHT(C1,LEN(C1)-FIND("|",C1)))

Pasting My message In Column "C"
277888|this is my message and i am different for every line

save the file

So the system i use i can select Ignore column "C"

Super Thanks Guys