MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Convert last name comma first name into two separate cells


Posted by Kristie Castellini on January 29, 2002 2:29 PM

I understand that a command exists that can change the contents of a single cell to separate cells. I want to be able to change my current column that contains LAST NAME, FIRST NAME, M.I. to one column of LAST NAME and one for FIRST NAME M.I.. Any ideas?
Thank you!!

Posted by Aladin Akyurek on January 29, 2002 2:38 PM

In B1 enter and copy down: =TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1)&" "&LEFT(A1,SEARCH(",",A1)-1))

where A1 houses the first entry that must be processed.

Posted by Jeanette Muscat on January 29, 2002 3:45 PM

If the name is in column B, then enter the formula in column C and D respectively
=RIGHT(B5,(LEN(B5)-(FIND(",",B5))))
=LEFT(B5,(FIND(",",B5)))

Posted by Aladin Akyurek on January 29, 2002 3:58 PM

Prompted by Jeanette's reply, I see I've put them all in one cell. Since you want them in 2 different cells,

in B1 enter: =TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1))
in C1 enter: =LEFT(A1,SEARCH(",",A1)-1)