1

I have a cell containing a text string like this:

A1 = John (John started 1/1/2020)

I'd like to remove everything in parenthesis so the end result would be:

A1 = John

I am trying to use REGEXREPLACE and I have the following formula:

=REGEXREPLACE(A1,"\(([A-Za-z]+)\)","")

However, this is not working. How do I remove the parenthesis and everything inside? The text in the parenthesis will vary, but will always be inside parenthesis.

For additional context - The above formula does work on a cell with a text string like this:

A2 = William (Bill)

When I use the above formula on cell A2, I get this:

A2 = William

Why does it work on A2 but not A1?

Improve this question
1
  • 2
    It's because A contains space, slash, and numbers while your regex defined only upper and lower case letters. Commented Jul 16, 2021 at 23:21

2 Answers 2

Reset to default
2

use:

=INDEX(IFNA(REGEXREPLACE(A1:A, " \((.+)\)", )))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

is it possible to do this if A1 == "John (John stared 1/12020)" (so in reverse)? The formula doesn't acknowledge this scenario.
@WilliamToscano not sure what do you mean by "reverse" can you share a copy of your sheet with example of desired result?
So in your screen shot, A1 is "John (John started in 1/12020)" and the formula works for that scenario. But is there a formula that works where A1 would equal "(John started in 1/12020) John", so I just flipped the 2 substrings around. The substring inside parentheses comes first, then " John".
Here is a copy: docs.google.com/spreadsheets/d/…
@WilliamToscano try: =ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A4, "\(.*\) (.*)")))
0

For anyone interested in being able to replace any number of sub-strings surrounded by parentheses, there's another posting here: Google sheets - How to remove ALL the parenthesis with text inside in a cell?

In that post, the regexreplace formula is used as follows:

=regexreplace(A1, "(\s\(.*?\))",)

to make a string like

we can (or can we...) replace multiple (try it) sub-strings with other text

become:

we can replace multiple sub-strings with other text

I was searching for this solution for a while, so I figured I'd post it here to make it that much easier to find.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.