Tweaking Formula Results: TRIM and MID-FIND for Names with and without parentheses

[Dais Helper]

Hello. I don't think this is a duplicate query.

I have a Roster I'm managing and some students have former surnames that I'm tracking because in older paperwork they'll have the former surname and newer paperwork they'll have the current surname.

I have their name column (D) listed like this: Surname, Forename -or- Surname (Former Surname), Forename

Ex. 1 in D3: Smith, Thomas​

Ex. 2 in D4: Baker (Abram), Kate​

PART I: The Name Flip

I have column F using the following formula:​

=TRIM(MID(D4&" "&D4,FIND(",",D4)+1,LEN(D4)))​

Results in F3: Thomas Smith -- excellent, no issues​

Results in F4: Kate Baker (Abram) -- This is okay-ish...​

However is there a way to tweak this formula to remove the parentheses and the text inside the parentheses to just read Kate Baker?​

PART II: The Former Surname Isolated

I have column E using the following formula I just found this morning:​

=MID(D4,FIND("(",D4)+1,FIND(")",D4)-FIND("(",D4)-1)​

Results in E4: Abram -- This is excellent​

Results in E3: #VALUE! -- yuck​

Is there a way to tweak this formula to return nothing in the cells like E3? I don't want #VALUE! showing.​

Currently I have conditional formatting making the text and the cell a shade of light grey so the resulting former surnames stand out in column E.​

My Current Results:

Surname, Forename (Col. D)	Former Surname (Col. E)	Forename Surname (Col. F)
Smith, Thomas	#VALUE!	Thomas Smith
Baker (Abram), Kate	Abram	Kate Baker (Abram)

What I want My Results to Be:

Surname, Forename (Col. D)	Former Surname (Col. E)	Forename Surname (Col. F)
Smith, Thomas		Thomas Smith
Baker (Abram), Kate	Abram	Kate Baker

Please note: I actually do not understand Formulas or how anything works. I have tried to read the explanations, but I think since I'm usually at work on a time crunch, I can't seem to process the words. I can manage to pop Formulas into cells and through trial and error. Then find one that works with replacing my cells instead. And sometimes I tweak a little bit based on ones I've tried before and get success. The VBA Coding scares me and I'm uncomfortable using it and so I implement it very, very infrequently, I don't attempt to tweak these much at all - just whatever the tutorial says. I prefer to work with the formulas.

Thank you very much in advance for your time and assistance.

 

[jasonb75]

You have the right formula in part 2, you simply need to trap the error

=IFERROR(MID(D4,FIND("(",D4)+1,FIND(")",D4)-FIND("(",D4)-1),"")

For part 1, try

=MID(D3,FIND(",",D3)+2,LEN(D3))&" "&LEFT(D3,FIND(" ",D3)-1)

 

[Dais Helper]

Ah-ha! Trapping... oh I see at the end the "" has nothing in it so nothing will result if it finds an 'error'. and if for some reason I wanted something to go there I could place it in the "" -- I tested with "0" and then removed it again. Awesomesauce!! Part 2 is now Part Perfect!! Thank you so very much. ?

Part 1 is being a brat with the new formula... ?

• It is retaining the commas in single surnames like Forename Crenshaw,
• It seems to be returning names with former surnames correctly and without commas like: Forename Irvin
• in 3-name compound surnames separated by spaces like De Leon Lopez it returns: De (no comma)
• in 2-name compound surnames like Del Busto it returns: Del (no comma)

Is there a way to modify the formula again?

 

[sparky2205]

This works, not quite as elegant, but it works:
=RIGHT(D3,LEN(D3)-FIND(",",D3)-1) & " " & IF(RIGHT(LEFT(D3,FIND(",",D3)-1),1)=")",LEFT(D3,FIND("(",D3)-2),LEFT(D3,FIND(",",D3)-1))

I didn't see your newer post. Seems you have more combinations than originally specified.

 

[Dais Helper]

This works, not quite as elegant, but it works:
=RIGHT(D3,LEN(D3)-FIND(",",D3)-1) & " " & IF(RIGHT(LEFT(D3,FIND(",",D3)-1),1)=")",LEFT(D3,FIND("(",D3)-2),LEFT(D3,FIND(",",D3)-1))

Awesomesauce strikes again! Thank you so very much. Now Part 1 is Part Perfect as well ?

 

[jasonb75]

Taking a different approach to the first formula, this looks like it works but I have only done a quick test.

=TRIM(SUBSTITUTE(MID(D3&" "&D3,FIND(",",D3&" "&D3)+1,LEN(D3)),IF(E3="","","("&E3&")"),""))

 

[Dais Helper]

Taking a different approach to the first formula, this looks like it works but I have only done a quick test.

=TRIM(SUBSTITUTE(MID(D3&" "&D3,FIND(",",D3&" "&D3)+1,LEN(D3)),IF(E3="","","("&E3&")"),""))

Cool! This one also works! Thank you ? ?