2

I have been using below formula which is working fine but i want to add one condition that is if data cell and the formula output result is same then it should add some comments with brackets as i did in Col"C".

Any help towards the problem will be appreciated.

=ArrayFormula(TRIM(REGEXREPLACE(A2:A,"(\*{3}.*?)(?:\s*?\.{3}DONE=>.*)?(\*{3})$","$1 $2")))

enter image description here

Link to Sheet

  • 1
    Why do you need to add it here? Why not use something like `=ARRAYFORMULA(IF(A2:A9=B2:B9, A2:A9 & " (No measure applicable)", B2:B9))` in Column C? – Wiktor Stribiżew Jun 04 '21 at 12:44
  • Because if i use the `=ARRAYFORMULA(IF(A2:A9=B2:B9, A2:A9 & " (No measure applicable)", B2:B9))` its result would be like this `*** HEDAL - 2020 COMPLETE - IB - 06-01-2021 *** (No measure applicable)` not like this `*** HEDAL - 2021 COMPLETE - IB - 06-01-2021 (No Measure Applicable) ***` –  Jun 04 '21 at 12:53
  • So, try `=ARRAYFORMULA(IF(A2:A9=B2:B9, REGEXREPLACE(A2:A9, "(\*+)$", "(No change) $1"), B2:B9))` then. – Wiktor Stribiżew Jun 04 '21 at 12:57
  • Thank you very much @Wiktor Stribiżew i missed this one just struggling to adjust it in above formula. Sir if it does not bother you can you please look into this question [Question](https://stackoverflow.com/questions/67827187/creating-a-hyperlink-in-google-sheets-via-formula/67828402#67828402) and please post an answer so i can accept it. –  Jun 04 '21 at 12:59
  • 1
    So, does the above work for you? – Wiktor Stribiżew Jun 04 '21 at 13:21

1 Answers1

1

You can add the following formula to Column C:

=ARRAYFORMULA(IF(A2:A9=B2:B9, REGEXREPLACE(A2:A9, "(\*+)$", "(No Measure Applicable) $1"), B2:B9))

This replaces the trailing one or more asterisks with (No Measure Applicable) + space + the asterisks captured into Group 1.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563