separate lastname from firstname

[enrico via OfficeKB.com]

if lastname and firstname is in one column, can you separate it into two
columns?

 

[Kassie]

Yes you can.
Use =LEFT(A1,SEARCH(" ",A1,1)-1) in B1
and =RIGHT(A1,LEN(A1)-LEN(B1)-1) in C1

--
HTH

Kassie

Replace xxx with hotmail

 

[enrico via OfficeKB.com]

what if you have two names and a middle name like:

Olmos, Edward James C.

 

[Kassie]

Then it becomes a little more complicated.

In B1 - =LEFT(A1,SEARCH(",",A1,1)-1);
In C1 - =MID(A1,LEN(B1)+3,SEARCH(" ",A1,LEN(B1)+2)-1);
In D1 - =MID(A1,LEN(B1)+LEN(C1)+4,SEARCH("
",A1,LEN(B1)+LEN(C1)+4)-LEN(B1)-LEN(C1)-4), all on one line; and
in E1 - =RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-LEN(D1)-4), and then you will end
up with "C.", if that's alright

--
HTH

Kassie

Replace xxx with hotmail