0

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)

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

See:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.