How to use multiple criteria with Regexreplace in Google sheet?

I am trying to correct the number/expression of room numbers that are entered by users with the following:

1- If the number contains ( / or \ or _ ), it will be replaced with "-".

2- If the number contains "iso" or "Bed", it should be removed or replaced with "".

The sample sheet is

Column A is to be entered by a user.

Column B is the result of arrayformula. The arrayformula is in B3 Cell. I couldn't add more than two regular expressions. Like, ("\|_|/").. what works is two only ("\|_").


It seems also complicated using two arrayformula functions in the same line.

What I want the result to be is: a number with "-" separator only. (I typed the expected results in Column C in order to modify the arrayformula to produce same result)

Answers 1

  • The backslash \ is used to escape the following character to give it a special meaning. For example, \d means any digit 0-9. To get a backslash, you need to escape it, as in \\. To make the match case-insensitive, use the (?i) flag. Try this:

    =arrayformula( regexreplace( regexreplace(A3:A, "(?i)iso|bed", ""), "[-/\\]", "-" ) )


    You only need one arrayformula() wrapper to array enable all of the formula.

Related Questions