Search code examples
google-sheetstrim

How to remove the integer behind the name if there is any? (google sheet)

enter image description here

Hi everyone,

My goal is to remove the integer behind the name (if exist) as shown in the screenshot above. Is there any trick to do this other than doing it manually? It is challenging for me because the name list is not consistent as some of the names does not have integer behind. Any help will be greatly appreciated!

Solution

  • In your situation, how about the following sample formula?

    Sample formula:

    =ARRAYFORMULA(REGEXREPLACE(A3:A,"\d+$",""))

    Result:

    enter image description here

    Note:

    • If you want to use TRIM for the value, how about =ARRAYFORMULA(TRIM(REGEXREPLACE(A3:A,"\d+$","")))?

    Reference:

    Added:

    From OP's following replying,

    Hi Tanaike, thanks for your input. I tested both formula. It works most of the time with the exception of Derrick Tan 1. Beside that, is it possible to remove the - as well if exist? I edited my question with new screenshot example, thank you.

    In this case, how about the following sample formula?

    Sample formula:

    =ARRAYFORMULA(TRIM(REGEXREPLACE(A3:A,"[-\s\d]+$","")))

    Result:

    enter image description here