# Remove Brackets From Names & Reverse Names With No Comma

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• I've unsuccessfully searched, so now I must ask...

Using a formula, how do I pull the exact name of a person from the middle of a text cell, since the length of each person's name varies in the original text cells, and the name within each text cell is not always preceded by (Ghost) but is always succeeded by “ (number)”. e.g., resolve to "Jarman, John" from text cell containing "(Ghost) Jarman, John (21)", or resolve to "Joe Smith" from "Joe Smith (2)"?

Additional Example: Column A needs to be converted to Column B:

Column A
(Ghost) Jarman, John (21)
Smith, Joe (2)
Johnson, Harvey (30)
(Ghost) Bob Jolly (-1)

Column B
Jarman, John
Smith, Joe
Johnson, Harvey
Jolly, Bob

My thanks in advance...

Maxi

• Re: Pull Exact Name From Middle Of Text Cell

Hi
I did this in three steps
first I remove the Ghost if it exists with this formula: =IF(LEFT(A2,1) = "(",RIGHT(A2,LEN(A2)-FIND(")",A2,1)-1),A2)

The result of your sample would be:
Column A
(Ghost) Jarman, John (21)
Smith, Joe (2)
Johnson, Harvey (30)
(Ghost) Bob Jolly (-1)

Column B
Jarman, John (21)
Smith, Joe (2)
Johnson, Harvey (30)
Bob Jolly (-1)

Then I remove the number at the end with this formula: =LEFT(B2,FIND("(",B2,1)-2)
Resulting in:
Column B
Jarman, John (21)
Smith, Joe (2)
Johnson, Harvey (30)
Bob Jolly (-1)

Column C
Jarman, John
Smith, Joe
Johnson, Harvey
Bob Jolly

Then I flip the names if I don't find a comma with: =IF(ISERROR(FIND(",",C2,1)),RIGHT(C2,LEN(C2)-FIND(" ",C2,1)) & ", " & LEFT(C2,FIND(" ",C2,1)-1),C2)
Resulting in:
Column C
Jarman, John
Smith, Joe
Johnson, Harvey
Bob Jolly

Column D
Jarman, John
Smith, Joe
Johnson, Harvey
Jolly, Bob

• Re: Pull Exact Name From Middle Of Text Cell

Spectacular!! It appears to do everything I need. Thanks so much; I'll plug the solution into the whole shebang and if I have further problems, I'll write. But it appears you've hit the nail on the head. Thanks again.
Maxi

• Re: Pull Exact Name From Middle Of Text Cell

Hi,

All in one.

=SUBSTITUTE(SUBSTITUTE(TRIM(IF(LEFT(A1)<>"(",MID(")"&A1,FIND(")",")"&A1)+1,FIND("(",A1)-1),MID(A1,FIND(")",A1)+1,SUM(FIND({")","("},A1,2)*{-1,1})-1))),", "," ")," ",", ")

HTH

• Re: Remove Brackets From Names &amp; Reverse Names With No Comma

It always happens to me here. I think I solved a problem elegantly and then some OZMVP comes along with a much more elegant way with a simple function that I have never heard of and makes me look like a total armature.

• Re: Remove Brackets From Names &amp; Reverse Names With No Comma

It's a second "Bingo"!

Great shooting Kris and thanks so much. (Although now I'll have to spend the next four hours figuring out how the formula works, but sure enough, it works.)

In the mean time, hold on Amirampeled, your solution works great too. And yours I could figure out -- reasonably well. So degrade yourself no longer, good friend, I was wrestling in the dark until you appeared. -- You're light years ahead of me.

My thanks to both of you.

Maxi

• Re: Remove Brackets From Names &amp; Reverse Names With No Comma

amirampeled, if you makes you feel better, I too tried a single function and gave-up in frustration

• Re: Remove Brackets From Names &amp; Reverse Names With No Comma

It does make me feel a little better. Thanks Dave.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!