I'm working on capturing and removing phone numbers on a Google Sheet from a dataset of text.

Right now, I am trying

=REGEXREPLACE(A2, "/\(?([0-9]{3})\)?([ .-]?)([0-9]{3})\2([0-9]{4})/", " ")

Where the following:

    `You are submitting your information to Example, which powers
... suite 000 San Diego, CA 00000(123) [email protected].`

Should become:

You are submitting your information to Example, which powers ... suite
000 San Diego, CA 00000 [email protected].

but Google says it is an invalid regex expression. stackoverflow is not letting me show the picture but here it is

Explanation/expression will be really appreciated. The Phone numbers can be any of the following formats:

(123) 456 7899 (Preferred)

(123).456.7899

(123)-456-7899 (Preferred)

123-456-7899

123 456 7899

1234567899

CodePudding user response：

Try this case-insensitive RE2 regex that does not use backreferences:

=regexreplace(A2, "(?i)\(?\d{3}\)?[-\s.]*\d{3}[-\s.]*\d{4}\s*(?:\(?preferred\)?)?(\b|[^\d])", " $1")