CODEWITHSUNDEEP
arraysregexgoogle-sheetsgoogle-sheets-formulaarray-formulas
SNT
SNT

Reputation: 1393

How to use REGEXREPLACE in Google Sheets for multiple strings

I have a column with such as Alpha :15s V1 .Beta :15s V2 .Delta :06s V3. I am trying to use regexreplace but it won't remove the V from the string. Here is the formula which I have used so far.

=REGEXREPLACE(T2, "[V]|[V1]-|[V2]|[V3]","")

How do I get this to work where I get the output just as Alpha :15s

Upvotes: 0

Views: 3711

Answers (2)

player0
player0

Reputation: 1

you can use this fx:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A, "(.*) V")))

0

which translates as: extract everything (.*) before space followed by V (.*) V

Upvotes: 1

Silvanas
Silvanas

Reputation: 613

If I get you correctly, you want to remove V1, V2 and V3 from the end of your text, for which you should use V[123] regex instead of [V]|[V1]-|[V2]|[V3] and to be further precise, use \s*V\d*$ regex.

enter image description here

Here \s* matches any whitespace then matches V and then matches one or more digits and finally $ matches end of line.

And the reason why it didn't work for you is, when you place characters in square brackets, it is called a character class and match any one character inside square bracket. Hence [V1] will match either V or 1 and replace that with empty string which is not what you wanted.

Upvotes: 1

Related Questions