I have the data in the attached sheet where i am trying to placer this line after the word COMPLETE but my formula places the line before the complete. You help towards the problem will be appreciated.
https://docs.google.com/spreadsheets/d/1jd7AVAvHwfznW_tpykhJb58cPvE7BLxtnJCq9fZy0WE/edit?usp=sharing
=ARRAYFORMULA(IF(A2:A9=B2:B9, REGEXREPLACE(A2:A9, "(COMPLETE)", "(No Measure Applicable) $1"), B2:B9))
See if this helps ?
=ARRAYFORMULA(IF(A2:A9=B2:B9, REGEXREPLACE(A2:A9, "(COMPLETE)", "$1 (No Measure Applicable)"), B2:B9))