Split name cell

[Aaron Stamboulieh]

I have a spreadsheet with one column that contains a list of names in the
following format:

LASTNAME, Firstname

Is it possible to automatically split this into two columns, one for
lastname, and one for firstname, using the comma as the basis of where to
make the split?

 

[Dan E]

Aaron,

Select your range containing the names
MAKE SURE you have an empty column to the right
From the menu "Data" -> "Text to Columns"
Choose delimited and choose comma as the delimiter

Dan E

 

[Aaron Stamboulieh]

Thanks everybody...I'm a network admin, my experience with Excel is limited.
Glad there are such helpful people around, even with such basic questions!
Much appreciated.

--
Aaron Stamboulieh - MCSA, A+

Sure. Suppose "Smith, Bill" (without the quotes) is in cell B2. You could
enter these three formulas:

In cell C2: =FIND(",",$B2)
In cell D2: =TRIM(MID($B2,$C2+1,999))
In cell E2: =LEFT($B2,$C2-1)

Here, the first formula finds the position of the **first** comma in the
name, and the other two formulas use that number to return what you want.

However, you might run into a problem if your names include something like:
Smith, Jr., Bill. In this case, you want to split using the **last** comma.
To find the position of the last comma, cell C2 needs to use a more complex
formula:

=FIND("#",SUBSTITUTE($B2,",","#",LEN($B2)-LEN(SUBSTITUTE($B2,",",""))))

Here, we count the number of characters in B2, and subtract the number of
characters with the commas stripped out, which gives us the number of
commas. We substitute a "#" sign for that final comma, and use FIND to
return the position of that # sign.

(Other than writing a VBA function, can anyone suggest an easier way to find
the last occurrence of a character in text? This is the only method that
I've found.)

Good luck.

Charley Kyd