Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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!!

Check out our Excel Resources

Re: Convert last name comma first name into two separate cells

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.


Re: Convert last name comma first name into two separate cells

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)))

Re: Convert last name comma first name into two separate cells

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)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.