In Excel, finding if cell contains any character other than letters, a dot . , single quotation and-CodePudding

I have the following table, and would like to identify the cells (as HIT) that contain characters other than

letters
dot .
single quotation

Which formula can I use for this? I've tried different functions, they don't seem to work.

CodePudding user response：

I think there will be a bunch of possibilities. Here is one using the logic that we will check every character in your string against all characters you'd like to exclude:

Formula in B2:

=IF(SUM(--ISERROR(SEARCH(MID(A2,SEQUENCE(LEN(A2)),1),"abcdefghijklmnopqrstuvwxyz'. "))),"Hit","No Hit")

Note: I deliberately included a space since you seems to be wanting to exclude that too.

Other options could be:

=IF(REDUCE(LOWER(A2),MID("abcdefghijklmnopqrstuvwxyz.' ",SEQUENCE(29),1),LAMBDA(a,b,SUBSTITUTE(a,b,"")))<>"","Hit","No Hit")

Or with FILTERXML():

=IF(ISERROR(FILTERXML("<t><s>"&LOWER(A2)&"</s></t>","//s[translate(., ""abcdefghijklmnopqrstuvwxyz.' "", '')!='']")),"No Hit","Hit")

Though these options are more verbose and both SUBSTITUTE() and FILTERXML() are case-sensitive whereas SEARCH() is not.

CodePudding user response：

So, perhaps easier to do:

IF(MAX(IFERROR(FIND(".",A1,1),0),IFERROR(FIND("'",A1,1),0))>0,"No Hit","Hit")

I did not include a space but that is easily edited in.