Complex concatenation/string rearrangement

[stefanaalten]

This is doing my head in! I would appreciate some help from anyone feeling clever!

Source cell may have name(s) in one of the following formats and needs to be rearranged as shown.

Case 1: Surname, Forename
e.g. "Lang, Fritz" -> "Fritz Lang"
Case 2: Surname (no forename)
e.g. "McG" -> "McG"
Case 3: Surname, Forename1 & Forename2
e.g. "Coen, Joel & Ethan" -> "Joel & Ethan Coen"
Case 4: Surname1, Forename1 & Surname2, Forename2
e.g. "Powell, Michael & Pressburger, Emeric" -> "Michael Powell & Emeric Pressburger"
Case 5: Surname1, Forename1 et al.
e.g. "Miller, George et al." -> "George Miller et al."
(there are no other cases in my source data)

I managed to solve it for some of the cases with complex nested formulae but am getting myself completely tangled up.

Many thanks in advance for any help!

 

[circledchicken]

Hi,


I think this should work for the cases you listed (using a few helper columns - assuming your data starts in A2):

• In B2 enter =SEARCH(",",A2,1) - This looks for the first comma
• In C2 enter =SEARCH("&",A2,1) - This looks for the first "&"
• In D2 enter =SEARCH(",",A2,SEARCH(",",A2,1)+1) - This looks for the second comma
• In E2 enter =SEARCH("et al.",A2,1) - This finds "et al."
• Finally in F2 enter =IF(ISNUMBER(E2),RIGHT(LEFT(A2,E2-2),LEN(LEFT(A2,E2-2))-B2-1)&" "&LEFT(LEFT(A2,E2-2),B2-1)&" et al.",IF(ISNUMBER(D2),RIGHT(LEFT(A2,C2-2),LEN(LEFT(A2,C2-2))-B2-1)&" "&LEFT(LEFT(A2,C2-2),B2-1)&" & "&RIGHT(RIGHT(A2,LEN(A2)-C2-1),LEN(RIGHT(A2,LEN(A2)-C2-1))-(D2-C2))&" "&LEFT(RIGHT(A2,LEN(A2)-C2-1),D2-C2-2),IF(ISNUMBER(B2),RIGHT(A2,LEN(A2)-B2-1)&" "&LEFT(A2,B2-1),A2)))

 

[circledchicken]

Hi,




I think this should work for the cases you listed (using a few helper columns - assuming your data starts in A2):

• In B2 enter =SEARCH(",",A2,1) - This looks for the first comma
• In C2 enter =SEARCH("&",A2,1) - This looks for the first "&"
• In D2 enter =SEARCH(",",A2,SEARCH(",",A2,1)+1) - This looks for the second comma
• In E2 enter =SEARCH("et al.",A2,1) - This finds "et al."
• Finally in F2 enter =IF(ISNUMBER(E2),RIGHT(LEFT(A2,E2-2),LEN(LEFT(A2,E2-2))-B2-1)&" "&LEFT(LEFT(A2,E2-2),B2-1)&" et al.",IF(ISNUMBER(D2),RIGHT(LEFT(A2,C2-2),LEN(LEFT(A2,C2-2))-B2-1)&" "&LEFT(LEFT(A2,C2-2),B2-1)&" & "&RIGHT(RIGHT(A2,LEN(A2)-C2-1),LEN(RIGHT(A2,LEN(A2)-C2-1))-(D2-C2))&" "&LEFT(RIGHT(A2,LEN(A2)-C2-1),D2-C2-2),IF(ISNUMBER(B2),RIGHT(A2,LEN(A2)-B2-1)&" "&LEFT(A2,B2-1),A2)))

If for some reason you didn't want the helper cells you could replace them in the formula and use:
=IF(ISNUMBER(SEARCH("et al.",A2,1)),RIGHT(LEFT(A2,SEARCH("et al.",A2,1)-2),LEN(LEFT(A2,SEARCH("et al.",A2,1)-2))-SEARCH(",",A2,1)-1)&" "&LEFT(LEFT(A2,SEARCH("et al.",A2,1)-2),SEARCH(",",A2,1)-1)&" et al.",IF(ISNUMBER(SEARCH(",",A2,SEARCH(",",A2,1)+1)),RIGHT(LEFT(A2,SEARCH("&",A2,1)-2),LEN(LEFT(A2,SEARCH("&",A2,1)-2))-SEARCH(",",A2,1)-1)&" "&LEFT(LEFT(A2,SEARCH("&",A2,1)-2),SEARCH(",",A2,1)-1)&" & "&RIGHT(RIGHT(A2,LEN(A2)-SEARCH("&",A2,1)-1),LEN(RIGHT(A2,LEN(A2)-SEARCH("&",A2,1)-1))-(SEARCH(",",A2,SEARCH(",",A2,1)+1)-SEARCH("&",A2,1)))&" "&LEFT(RIGHT(A2,LEN(A2)-SEARCH("&",A2,1)-1),SEARCH(",",A2,SEARCH(",",A2,1)+1)-SEARCH("&",A2,1)-2),IF(ISNUMBER(SEARCH(",",A2,1)),RIGHT(A2,LEN(A2)-SEARCH(",",A2,1)-1)&" "&LEFT(A2,SEARCH(",",A2,1)-1),A2)))

But that makes it even more unreadable so I wouldn't recommend it!

 

[stefanaalten]

Many thanks!