Splitting first and family name

P

PCOR

Hi
I have along list of names, like WENDY ROCHESTER which I want to change to
ROCHESTER WINDY
Can a kind sole tell me how?
Thanks
 
T

Tod

Assuming that the format is always going to be:

FirstName LastName

'In this example, range is A2:A100
Sub SwitchIt()
Dim Cell As Range

For Each Cell In Range("A2:A100")
If Len(Cell.Value) <> 0 Then
Cell.Value = Mid(Cell.Value, Application.Find
(" ", Cell.Value, 1) + 1, 100) & " " & Left(Cell.Value,
Application.Find(" ", Cell.Value) - 1)
End If
Next Cell
End Sub
 
P

PCOR

Thanks very much...works great

Tod said:
Assuming that the format is always going to be:

FirstName LastName

'In this example, range is A2:A100
Sub SwitchIt()
Dim Cell As Range

For Each Cell In Range("A2:A100")
If Len(Cell.Value) <> 0 Then
Cell.Value = Mid(Cell.Value, Application.Find
(" ", Cell.Value, 1) + 1, 100) & " " & Left(Cell.Value,
Application.Find(" ", Cell.Value) - 1)
End If
Next Cell
End Sub
Click to expand...
 
D

David McRitchie

Glad you go the answer you wanted, but if you are putting
the surname first why aren't you either placing a comma
afterward the surname (like in the US) or making sure that the surname
is capitalized, and the firstname is proper case as asked for
on some international registration forms.
 
Top