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

[caliray]

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.

[mahfiaz]

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

name separator.ods

[oweng]

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

…rather than your suggested:

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

[Nemo_bis]

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

[mariosv]

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

[oweng]

This is a good idea, although I am not clear from the question whether the suffixes are to be retained (as shown in first example) or dispensed with (as shown in second example).

[caliray]

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.

[oweng]

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.