Easy one...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, I have a list of names in the fomrat John A Doe, Joan Bennett, etc (no
commas, some have middle initial, some don't). How do I get Doe John A, and
Bennett Joan (last name first name middle initial - commas OK) I tried the
Pearson example, but it had commas and the last name was first, so it didn't
work for my problem.

Thank you.

NWO Mark
 
Try this UDF:

Function swapit(r As Range) As String
s = Split(r.Value, " ")
If UBound(s) = 2 Then
swapit = s(2) & " " & s(0) & " " & s(1)
Else
swapit = s(1) & " " & s(0)
End If
End Function
 
If all parts of a name are in seperate fields, like:
A B C
John A Doe
Joan Bennett

Than enter this in column D:
=TRIM(CONCATENATE(C1,", ",A1,," ",B1))
 
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,MID(A1,FIND(" ",A1)+1,99)&"
"&LEFT(A1,FIND(" ",A1)-1),MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,99)&"
"&LEFT(A1,FIND(" ",A1))&MID(A1,FIND(" ",A1)+1,1))

Note: The above formula will work with or without middle name as along as
middle name contain a single character.
 
Fails on names like:

Martin St Louis (real name of a hockey player)
Oscar De La Hoya (real name of a boxer)
 
Ok, I have a list of names in the fomrat John A Doe, Joan Bennett, etc (no
commas, some have middle initial, some don't). How do I get Doe John A, and
Bennett Joan (last name first name middle initial - commas OK) I tried the
Pearson example, but it had commas and the last name was first, so it didn't
work for my problem.

Thank you.

NWO Mark
Click to expand...

The previously offered solutions seem to fail on those with multiple last
names, e.g. Oscar de la Hoya

My solution, which probably won't work all the time, assumes that
if a single letter follows the first name,
then that single letter is the middle,
otherwise it is part of the last name.

It requires a UDF.

To do this, <alt-F11> opens the VB Editor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

To use this, enter a formula =LNFNMI(cell_ref) into some cell.

If you prefer to NOT have the comma after the Last Name, in the code below,
merely remove it from the CONST definition of sRes.

===========================================
Option Explicit
Function LNFNMI(str As String) As String
Dim re As Object
Const sPattern As String = "(^\w+)\s+(\w\b)?\s*(.*$)"
Const sRes As String = "$3, $1 $2"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPattern

LNFNMI = re.Replace(str, sRes)
End Function
====================================
--ron
 
I beware of that, I quote on my reply: "Note: The above formula will work
with or without middle name as along as middle name contain a single
character."
 
Back
Top