Use Multiple Criteria with Regexreplace – Google Sheets

google sheets

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 https://docs.google.com/spreadsheets/d/1U-daD0CEPRLoCP7xCLT9bSMK9yN3ll8QQQfNk7-OZDs/edit#gid=1418057437.

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 ("\|_").

=ArrayFormula(REGEXREPLACE((ArrayFormula(REGEXREPLACE(UPPER(A3:A),("ISO|BED"),""))),("\|_"),"-"))

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)

Best Answer

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", ""), "[-/\\]", "-" ) )

See:

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

Related Topic