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
--
Gary''s Student - gsnu200742


"NWO" wrote:

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

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

"NWO" wrote:

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

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


"NWO" wrote:

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

Fails on names like:

Martin St Louis (real name of a hockey player)
Oscar De La Hoya (real name of a boxer)

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=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.


"NWO" wrote:

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

On Tue, 4 Sep 2007 16:36:00 -0700, NWO wrote:

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

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

"T. Valko" wrote:

Fails on names like:

Martin St Louis (real name of a hockey player)
Oscar De La Hoya (real name of a boxer)

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=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.


"NWO" wrote:

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

Why do you assume this is easy?


--
Regards,

Peo Sjoblom



"NWO" wrote in message
...
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