Regexreplace with ignore-CodePudding

I want to regexreplace the number 2 in some rows with "double", but I want to ignore if the numbers are 12, 22, 32 etc.

EDIT:

What if I add letters to the numbers? Like this:

CodePudding user response：

There are a few ways to tackle this, but using ^2$ will only match 2

=ARRAYFORMULA(
  IF(ISBLANK(A1:A),,
   IF(
    REGEXMATCH(
     TO_TEXT(A1:A),
     "^2$"),
    "double",
    A1:A)))

Using REGEXMATCH instead of REGEXREPLACE allows us to return the values in A1:A as actual numerical values instead of the text value the formula requires.

Working with a2 etc, this will allow for optional letters before the 2

=ARRAYFORMULA(
  IF(ISBLANK(A1:A),,
   IF(
    REGEXMATCH(
     TO_TEXT(A1:A),
     "^(?i)[A-Z] ?2$"),
    "double",
    A1:A)))