I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.

Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental changes together???? Help!

Thank you

Copy cells to Word.
In Word:
Convert table to text (separate text with paragraph marks)
Go to Edit Replace
Enable Wildcards
Replace ( [A-Za-z]{1,}^13) with £\1
note: space before £ in previous
Replace (A-Za-z ]{1,})£([A-Za-z]{1,}) with \2, \1
Copy and paste back to Excel

"Krexroth" wrote:

I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.

Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental changes together???? Help!

Thank you

I got this to work for me by searching for the blanks in between names
within an entry. If the entry contains two blanks, I assumed the
characters between the blanks were either a middle name or an initial.

If your data is in column A1, for instance "Jo Ann Smith", you
indicated you need the last name in column B, the first name in C, and
the Middle Initial or Middle Name in column D.

In B1, enter this formula: it returns the letters after the last " "
(space) in the entry.
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),MID(A1,FIND("
",A1,1)+1,LEN(A1)),MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,LEN(A1)))

In C1, enter this formula to find the first name (the letters before
the first space):
=MID(A1,1,FIND(" ",A1,1)-1)

In D1, enter this formula. It returns the letters between two spaces if
two spaces exist:
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),"",MID(A1,FIND("
",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1))

Note that you may still need to review your list for entries with "Sr."
or "Jr", since these formulas will treat those titles as last names.

Thanks Dave. I had subsequently found this solution (for MI or MName only) at
http://www.exceltip.com/st/Extract_t...Excel/370.html
IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)), FIND("
",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+
1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND("
",A1)+1))-FIND(" ",A1)-1))

I appreciate your solution as it breaks the syntax down into more easily
understood chunks. Thanks Again.




"Dave O" wrote:

I got this to work for me by searching for the blanks in between names
within an entry. If the entry contains two blanks, I assumed the
characters between the blanks were either a middle name or an initial.

If your data is in column A1, for instance "Jo Ann Smith", you
indicated you need the last name in column B, the first name in C, and
the Middle Initial or Middle Name in column D.

In B1, enter this formula: it returns the letters after the last " "
(space) in the entry.
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),MID(A1,FIND("
",A1,1)+1,LEN(A1)),MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,LEN(A1)))

In C1, enter this formula to find the first name (the letters before
the first space):
=MID(A1,1,FIND(" ",A1,1)-1)

In D1, enter this formula. It returns the letters between two spaces if
two spaces exist:
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),"",MID(A1,FIND("
",A1,1)+1,FIND(" ",A1,FIND(" ",A1,1)+1)-FIND(" ",A1,1)-1))

Note that you may still need to review your list for entries with "Sr."
or "Jr", since these formulas will treat those titles as last names.

Have a look at Chip Pearson's First/Last examples. Also has a downloadable
workbook to go with the examples.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben Excel MVP

On Fri, 7 Jan 2005 11:23:05 -0800, "Krexroth"
wrote:

I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.

Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental changes together???? Help!

Thank you

I have, but those solutions seem to address the format LAST, FIrst MI to pase
them into individual pieces. The comma helps alot in that instance.
Thanks a lot though.

"Gord Dibben" wrote:

Have a look at Chip Pearson's First/Last examples. Also has a downloadable
workbook to go with the examples.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben Excel MVP

On Fri, 7 Jan 2005 11:23:05 -0800, "Krexroth"
wrote:

I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.

Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental changes together???? Help!

Thank you