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
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
