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: Remove Brackets From Names & 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 & 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

Participate now!

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

Register Yourself Login