# Revision history [back]

### 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.  2 retagged manj_k 10975 ●15 ●68 ●101 ### 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.