Reversing first and last names in column

[Helene G.]

An organization I volunteer for has a database setup in Excel. The column
for the name is set up this way: Jones, Thomas T. Now they want to change it
to Column A - Thomas T. and Column B - Jones. Is there a way to do this
without retyping all the names. We're all pretty new to Excel and don't know
were to begin. I'd really appreciate any help with this. Thanks.

Helene

 

[Peo Sjoblom]

Use data>text to columns, delimited select comma as delimiter, that will
split them at the comma,
then just select column A, right click and do cut, select column C, right
click and do insert cut cells
Or use a formula

To get the first name

=TRIM(MID(A1,FIND(",",A1)+1,255))

last

=LEFT(A1,FIND(",",A1)-1)

when you are done, select both columns and do edit>copy,then edit>paste
special as values
in place. Finally delete A

 

[David McRitchie]

Hi Helene,

Yyou could insert two columns after column A
B1: =TRIM(MID(A1,FIND(",",A1)+1,99))
C1: =LEFT(A1,FIND(",",A1)-1)

Will accept single space or no space after the comma.

Use the fill handle to copy down, the Select columns B & C
Copy (ctrl+C), edit, paste special, values,
then delete the original column A

 

[John Wilson]

Helene,

One way......
If your names are in column "A".
Insert two "helper" columns (blank columns) between
to the right of Column "A" (unless they're blank already).
In Column "B" use this formula:
=MID(A1,FIND(",",A1)+2,LEN(A1)
In Column "C" use this formula:
=LEFT(A1,FIND(",",A1)-1)
Now, copy down as far as you need to go.
Next would be to highlight all of the entries in Columns B & C
together and then select Copy, then Paste Special / Values.
If all looks well, you can now just delete column "A".

John

 

[Helene G.]

Thank you everyone. I tried these and I've got it. You're wonderful!!!!!

Helene

 

[mudraker]

I know you have solved your problem.

But for your future reference you could have also parsed the name
using the following method as long as there are a couple of empt
columns next to the name list


Select the column or range containing the name list
From Data Menu select Text To Column
Select Delimited option then Next button
Tick Comma box then Next button
Finish butto