Non A-Z or 0-9 Characters

[tmurphy686]

I need to create a cell with a "Y" or "N" if a different cell has a non A-Z or 0-9 character in it.

Example:

A B
1-JOHN A SMITH N
2-JOHN A. SMITH Y
3-JOHN $% SMITH Y

I'd prefer to do this without VB - Thanks!

 

[Mackers]

Hi

I assume you also want to allow full stops. For your cell in A6, as an array function (control+shift+enter):

=IF(SUM(--ISERROR(SEARCH(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)[COLOR=#0000ff],"abcdefghijklmnopqrstuvwxyz 0123456789."[/COLOR]))),"N", "Y")

If you need to allow any more characters, simply add them into the blue string.

Hope that helps

Mackers

 

[bosco_yip]

Or this non-array formula :

=IF(INDEX(FREQUENCY(CODE(MID(A1,ROW($1:$99),1)&"~"),{64,91}),2)=LEN(SUBSTITUTE(A1," ",)),"Y","N")

Regards