SEARCH or COUNTIF excel range with INDIRECT

Issue

I try to make this formula: =IF(COUNT(SEARCH(MID(D2;ROW(INDIRECT("1:"&LEN(D2)));1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=0;"NO";"OK")

works like this formula:
=IF(COUNT(SEARCH({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"};D2))=0;"NO";"OK")

but its not working when cell starts with no letters.

Image attached

Solution

What you are after is probably something like:

=IF(SUM(IFERROR(SEARCH(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",SEQUENCE(26),1),D2),0)),"OK","NO")

For versions prior to ms365, try:

=IF(IFERROR(LOOKUP(99^99,SEARCH(MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW($1:$26),1),D2)),0),"OK","NO")

This is build to be auto-remove implicit intersection so the user won’t have to use CSE.

Answered By – JvdV

Answer Checked By – Katrina (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.