Converting from first name first to last name first

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

I have a listing of employee names in first name first
format. I want to change them to last name first. Is
there an easy way to do this without re-typing the whole
list?
 
Will,

One of the difficulties here is identifying what is a name and not fluff in
the name (Bob Phillips Jr. for instance, or Ian St John). Here is a regular
expressions utility that does all that, and a sub that calls it, and swaps
the names.

Sub NameSwap()
Dim sLast As String
Dim iPos As Long
With ActiveCell
sLast = LastName(.Value)
iPos = InStr(1, .Value, sLast)
.Value = sLast & ", " & Left(.Value, iPos - 1)
End With
End Sub

Function LastName(nme As String)
Const sRegExp = "\b([a-z]+ +)*(O'|Mc|Mac)?[A-Z]" & _
"(\w+\S?)*(-[A-Z](\w+\S?)*)?\b(?=(( +)" & _
"(Sr\.?|Snr\.?|Jr\.?|Jnr\.?|[IVX][IVX]*))|,|\s*$)"
Dim oRegExp As Object, oResult As Object
On Error GoTo RE_error
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = sRegExp
oRegExp.Global = True
Set oResult = oRegExp.Execute(nme)
LastName = IIf(oResult.Count > 0, oResult(0).Value, "")
GoTo RE_Exit
RE_error:
LastName = "RE Error"
RE_Exit:
Set oRegExp = Nothing
On Error GoTo 0
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Will said:
I have a listing of employee names in first name first
format. I want to change them to last name first. Is
there an easy way to do this without re-typing the whole
list?
Click to expand...

Have a look at this solution previously posted by Norman Harker
http://tinyurl.com/2n2dp
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

=SUBSTITUTE(MID(A6,FIND(".",A6)+2,100),".","")&", "&LEFT(A6,FIND(".",A6))
 
Back
Top