PDA

View Full Version : Solved: how to swap firstname lastname in cell?



DaveK
07-23-2008, 02:25 PM
Hi,
I am new to this forum... and have a request for someone to help me
figure out how to have VBA for Excel to modify column of names:

Example cell has firstname lastname:
John Barber

What I need is:
Barber, John


In other words, search the cell until space character found, save that text as FIRSTNAME, then continue cell searching character-by-character until another space, and save that text as LASTNAME.
Then re-write cell, or maybe adjacent column (that would be better so I can tell it worked correctly), and put:

LASTNAME, FIRSTNAME

(that is a comma and then a single space between LASTNAME and FIRSTNAME )

Your help is much appreciated!

Dave

mdmackillop
07-23-2008, 02:37 PM
Sub Test()
Dim nm, Cel As Range
For Each Cel In ActiveSheet.Range("MyNames")
nm = Split(Cel)
Cel.Offset(, 1) = nm(1) & ", " & nm(0)
Next
End Sub

Bob Phillips
07-23-2008, 03:01 PM
=SUBSTITUTE(MID(A1&", "&A1,FIND(" ",A1)+1,99),RIGHT(A1,FIND(" ",A1)+2),"")

DaveK
07-24-2008, 08:42 AM
Thank You! This forum is really a wonderful place for help!

Dave
:clap2: