Excel: Separating data in one cell (full name) into two (first, last name).

[Trey22]

I have a list of over 800 names in an Excel spreadsheet, the problem being that each persons full name is all in one cell:

Smith, John A.

What I want to do is separate the full name so the first name/middle initial is in one column and the last names is in another.

I hope Excel has to have a way to read a cell and copy everything after a character (in this case the comma), copy it to a new cell and delete it from the original.

Thanks in advance, Trey.

 

[DJFuji]

i'm not an excel guru so i don't know how to do that inherently, but i'm pretty sure you can do that with VBA or VBScript. Look into that.

 

[TheLogLady]

I don't have a copy of excel on this PC, but try this:

>> Select the entire column of full names, and copy (ctrl-c).
>> Open notepad.
>> Paste (ctrl-v) data into notepad.
>> From notepad, save as "filename.csv"
>> Open *.csv in excel.

 

[tooltime]

easy. first though make a backup copy

under Data, use Text to Columns. it might take a few trys if you haven't used this wizard. i've used it many times for just that and it works great, once you've been through it a couple times or so

 

[Trey22]

Great tooltime, that seems to have done the trick! I'm a total noob when it comes to Excel. Thanks all for the responses... Cheers, Trey.

 

[tooltime]

yw. don't know if you need this too but you can do a formula to make the delimited data Title Case and paste special the values into new colums. ie =PROPER(A1) that should make the contents of a1 title case then copy and paste special the 'values' into a new cell