Is there a way to check if a SUBSTITUTION was performed in an Excel cell and if not, give customized text warning in the new cell?

Taimaishoo 0
Viewed 29

I have a cell in Excel with nested SUBSTITUTIONs

=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F11,35500000,38000000),28700000,31600000),23500000,25000000),19900000,20200000),37500000,38000000),30700000,31600000),21400000,20200000),23500000,25000000))

If F11 doesn't contain any of these numbers, it will just put the original number in the new cell.

something here 35   something here 35

What I would like to happen is that if there was no SUBSTITUION performed, place a warning in the new cell that SUSBSTITUTION wasn't performed.

something here 35   35 not found. check your SUBSTITUIONS

Would this be possible and how would I go about doing it?

Thanks in advance!

substitution excel
1 Answers

With the New Dynamic Formula we can do it all:

First put the target and replacements in their own range:

enter image description here

Then using the following formula:

=LET(
   o,A1,
   t,$H$2:$H$9,
   r,$I$2:$I$9,
   rpl,REDUCE(o,SEQUENCE(ROWS(t)),LAMBDA(a,b,SUBSTITUTE(a,INDEX(t,b),INDEX(r,b)))),
   IF(rpl=o,"Check Your Substring",rpl)
     )

We get:

enter image description here

If you do not want to go the table route then you can hard code the numbers in vertical arrays:

=LET(
   o,A1,
   t,{35500000;28700000;23500000;19900000;37500000;30700000;21400000;23500000},
   r,{38000000;31600000;25000000;20200000;38000000;31600000;20200000;25000000},
   rpl,REDUCE(o,SEQUENCE(ROWS(t)),LAMBDA(a,b,SUBSTITUTE(a,INDEX(t,b),INDEX(r,b)))),
   IF(rpl=o,"Check Your Substring",rpl)
     )
Scott Craner0
Related