Divide "Name" into First & Last

  • Thread starter Thread starter Nancy Docken
  • Start date Start date
N

Nancy Docken

Hi,

I've been sent a spreadsheet of people, but the person who
built this just has a name column. (Example: Jones, John)

Is there a formula I can use to make two columns of this
one? I want to end up with Last Name and First Name in
separate columns.
 
=a1&" "&b1

fill down as needed.

when done, copy -> paste special, values -> delete a:b

HT
 
=LEFT(A1,FIND(" ",A1))
=MID(A1,FIND(" ",A1,1)+1,LEN(A1))
try these in two separate columns with the name in A1.
 
Firefytr must have misread the question.

with "Jones, John" in A1,
B1, put: =LEFT(A1,FIND(",",A1)-1)
C1, put: =TRIM(RIGHT(A1,LEN(A1)-LEN(B1)-2))

once you have those, copy them and paste special as values. then you can
delete the column of origin.
 
Hi Dave,

I appreciate the help, but I must be doing something
wrong. The formulae only return "#VALUE!". Can you help me
correct this?
 
Hi Gerry,

Both you and Dave left similar formulae, but trying both I
only get "#VALUE!" Any suggestions on where I went wrong?
 
Oh, what a silly me!!! I just realized that I'd put the
formula into row A2, and there was nothing in A1. Sorry.
Of course it works!!!

Thanks again
 
Hi Nancy
a solution without formulas:
- select your column
- goto 'Data - Text in columns'
- choose the coma as delimiter and finish the wizard
 
Oh, what a silly me!!! I just realized that I'd put the
formula into row A2, and there was nothing in A1. Sorry.
Of course it works!!!

Thanks again
 
Select the column of data, do Data / text To columns / delimited / tick 'space'
as delimiter - Hit OK or finish

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Hi Dave,

I appreciate the help, but I must be doing something
wrong. The formulae only return "#VALUE!". Can you help me
correct this?
Click to expand...
<snip>
 
Nancy

Insert a column to the right of the name column.

Data>Text to Columns>Delimited>Next>Comma>Finish.

Gord Dibben Excel MVP
 
Back
Top