Ask Your Question
0

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

asked 2013-05-25 13:13:26 +0100

caliray gravatar image

updated 2014-01-15 20:43:07 +0100

manj_k gravatar image

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 flag offensive 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

4 Answers

Sort by » oldest newest most voted
1

answered 2013-05-26 14:43:14 +0100

mahfiaz gravatar image

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

name separator.ods

edit flag offensive delete link more
0

answered 2013-05-25 16:01:38 +0100

oweng gravatar image

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.

edit flag offensive delete link more

Comments

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

Nemo_bis gravatar imageNemo_bis ( 2015-12-13 01:23:41 +0100 )edit
0

answered 2013-05-25 16:30:57 +0100

m.a.riosv gravatar image

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

edit flag offensive delete link more

Comments

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

oweng gravatar imageoweng ( 2013-05-26 02:01:13 +0100 )edit
0

answered 2013-05-25 17:30:28 +0100

caliray gravatar image

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.

edit flag offensive delete link more

Comments

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.

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

Question Tools

Stats

Asked: 2013-05-25 13:13:26 +0100

Seen: 3,305 times

Last updated: May 26 '13