Quick and easy for the experts...

[MaxPeck]

Have in column b:

Smith*John

Need to split last and first name into separate cells. Hitch: some of the cells in B have entries like "Bob's Market" . The only cells I want to modify have an asterisk seperating the last and first names.

Thanks for the help, left my Excel book at home, can't find the stinkin thing on PDF on my HDD>

Thanks again list.

 

[plettieri]

Hi

You could try DATA | TEXT TO COLUMNS and use the * as the delimiter....

hope this helps

plettieri

 

[jcvoth]

I'd do the following

First name = LEFT(E2,FIND("*",E2,1)-1)
Last name = RIGHT(E2,FIND("*",E2,1)-1)

Where the name John*Paul is in E2.

Good luck!

-Jarrod

 

[jcvoth]

Here is the same formula as before, but with protection from the "Hitch".

First name =IF(ISERROR(FIND("*",E2)),E2,LEFT(E2,FIND("*",E2,1)-1))
Last Name =IF(ISERROR(FIND("*",E2,1)),"",RIGHT(E2,FIND("*",E2,1)-1))

This searches the name for the * and blanks out the last name if it is not found, and puts the whole name into first name.

-Jarrod