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

Dais Helper

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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), KateAbramKate Baker (Abram)

What I want My Results to Be:
Surname, Forename (Col. D)Former Surname (Col. E)Forename Surname (Col. F)
Smith, ThomasThomas Smith
Baker (Abram), KateAbramKate 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. :)
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Feb 6, 2013
Messages
296
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 🙏 🎉
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top