# How to reorder first name, last name in a text string? [closed]

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.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-01 22:00:16.529365

Sort by » oldest newest most voted

If this doesn't get you going, I'd suggest giving up:

name separator.ods

more

Made this change: change your function separator from a comma to a semi-colon (Tools > Options... > LibreOffice Calc > Formula).

This =LEFT(A2;SEARCH("[ ][A-Z]";A2)) returned #VALUE!

I put this =TRIM(LEFT(A2;SEARCH("[ ][&][ ]";A2))) in B2 and it returned #VALUE!

Since the above didn't work to return the correct value in B2, I did not try this =TRIM(RIGHT(A2;(LEN(A2)-LEN(B2)))) or this =RIGHT(C2;LEN(C2)-2) # to give "RALPH H JR" in D2

Nonetheless, I appreciate the effort to help with this.

more

The #VALUE! error may mean your initial cell (and value) is not in an appropriate (text) format. If the formula is mal-formed (e.g., semi-colon when comma is expected) you will receive a 508 error.

( 2013-05-26 01:55:16 +0100 )edit

This is a rushed response and not a complete answer. Firstly change your function separator from a comma to a semi-colon (Tools > Options... > LibreOffice Calc > Formula). The help page examples will make far more sense and you will be able to exchange data internationally.

This type of string parsing is best handled externally to Calc by a language like Perl, however, to obtain "RHODES" use this form of regular expression:

=LEFT(A2;SEARCH("[ ][A-Z]";A2))


=LEFT(A2;FIND(" ";A2;1)-1)


Overall, I would first separate your two names using something like:

=TRIM(LEFT(A2;SEARCH("[ ][&][ ]";A2))) # to give "RHODES TEDDI J" in B2


...and:

=TRIM(RIGHT(A2;(LEN(A2)-LEN(B2)))) # to give "& RALPH H JR" in C2


...and:

=RIGHT(C2;LEN(C2)-2) # to give "RALPH H JR" in D2


You can then parse the strings in B2 and D2 more easily, using the same techniques I have indicated.

more

Good suggestions, I think however you first need to go to Options > Calc > Calculations > Activate regex.

( 2015-12-13 01:23:41 +0100 )edit

With the function SUBSTITUTE() is possible eliminate the "JR" or "SENIOR" from the string. =SUBSTITUTE(A2;" JR";"")

more