Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

How to reorder first name, last name in a text string?

I have a spreadsheet with a list of names. The names are listed as last name first name, e.g. Smith John, but some of the names are very complex. e.g. RHODES TEDDI J & RALPH H JR

I need to reorder the name found in cell A2 to this format. TEDDI J & RALPH H RHODES JR. I can isolate each component of last name first name and concatenate the results and the name will be in the first name, last name order I need.

The problem is when the name comes in three parts, last name, first name plus initials, and then a suffix of JR or SENIOR, etc.

=LEFT(A2,FIND(" ",A2,1)-1) gets me the last name RHODES but
=RIGHT(A2,LEN(A2)-FIND(" ",A2)) returns TEDDI J & RALPH H JR

when what I need to return is TEDDI J & RALPH H without the JR

This formula =MID(A2,SEARCH("[^[:space:]]+$",A2),LEN(A2)) was offered on the Openoffice forum, but returns #VALUE!

I appreciate any help with the MID formula, especially references that help me dissect the MID formula so I understand how each part works. With that knowledge, I'd be able to help others as you help me.

click to hide/show revision 2
retagged

How to reorder first name, last name in a text string?

I have a spreadsheet with a list of names. The names are listed as last name first name, e.g. Smith John, but some of the names are very complex. e.g. RHODES TEDDI J & RALPH H JR

I need to reorder the name found in cell A2 to this format. TEDDI J & RALPH H RHODES JR. I can isolate each component of last name first name and concatenate the results and the name will be in the first name, last name order I need.

The problem is when the name comes in three parts, last name, first name plus initials, and then a suffix of JR or SENIOR, etc.

=LEFT(A2,FIND(" ",A2,1)-1) gets me the last name RHODES but
=RIGHT(A2,LEN(A2)-FIND(" ",A2)) returns TEDDI J & RALPH H JR

when what I need to return is TEDDI J & RALPH H without the JR

This formula =MID(A2,SEARCH("[^[:space:]]+$",A2),LEN(A2)) was offered on the Openoffice forum, but returns #VALUE!

I appreciate any help with the MID formula, especially references that help me dissect the MID formula so I understand how each part works. With that knowledge, I'd be able to help others as you help me.