Change firstname lastname to lastname first name in a column

[cadonn]

Hi, I have an excel spreadsheet where I have a column of user names an
they are listing as Mary Jones, Tim Betts etc. There is a spac
between the First Name and Last Name. I would like to change thi
column to show Jones, Mary and Betts, Tim. So lastname, firstname. I
there a way to do this as I have a lot of names to change. Thank you.

Cat

 

[Frank Kabel]

Hi Caty
one way suing a helper column. enter the following in B1
=MID(A1,FIND(" ",A1)+1,255) & " " & LEFT(A1,FIND(" ",A1)-1)
copy this down for all rows.

after this copy column B and insert it again with 'Edit - Paste
Special - Values'. After this you can delete column A if you like

 

[CLR]

Highlight the column (assume A), then do Data > Text to columns > Delimited

Next > check the Space box > Next > Finish...........this will separate

the two names into two columns, A and B........then in C1 put this formula
and copy it down........

=B1&", "&A1

You can then do Copy > Paste Special > Values on column C and delete columns
A and B if you wish..........

Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

This maco will change your current column to what is desired.
Sub switchnames()
For Each n In Selection
x = InStr(n, " ")
n.Value = Right(n, Len(n) - x) & ", " & Left(n, x)
Next n
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Highlight the column (assume A), then do Data > Text to columns > Delimited

Next > check the Space box > Next > Finish...........this will separate

the two names into two columns, A and B........then in C1 put this formula
and copy it down........

=B1&", "&A1

You can then do Copy > Paste Special > Values on column C and delete columns
A and B if you wish..........

Vaya con Dios,
Chuck, CABGx3