Special Character Formula

[luisitocarrion1900]

I need something like this =ISNUMBER(FIND("*","!",P6)) that if a cell contain any specific special character return true or false. For example, if the cell contain any other character than a number or letter it will return a true or false.

 

[Ron Coderre]

Maybe something like this

First, on a separate tab, put these sequential numbers, beginning in cell A1:

1 to 47
58 to 64
91 to 96
123 to 255

Then, copy this formula down through B193

B1: =CHAR(A1)

Create a range name: rngInvalid that references B1:B193

Now, on your working tab...
A1: any string you want to test (example: 123AB@ljh)

B1: =IF(COUNT(INDEX(FIND(rngInvalid,A1),0)),"No","Yes")

Is that something you can work with?

 

[DRSteele]

If cell A1 contains anything other than numbers (0-9) or letters (both uppercase and lowercase, standard UK alphabet), this formula will report TRUE, otherwise FALSE.

[FONT=Verdana]=IF(SUM(--(UNICODE(A1)={48;49;50;51;52;53;54;55;56;57;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;97;98;99;100;101;102;103;104;105;106;107;108;109;110;111;112;113;114;115;116;117;118;119;120;121;122}))>0,FALSE,TRUE)[/FONT]

Those numbers are the UNICODE numbers. 48 to 57 are 0-9, 65 to 90 are uppercase A-Z, and 97 to 122 are lowercase a-z.

We use the UNICODE and UNICHAR functions to determine which characters are included in your accepted list. If you would like to see what each character is, try =UNICHAR(1) all the way to

=UNICHAR(65533). Or see here for the entire map. https://www.grogllc.com/unicodetable.html


P.S. For extra kicks and giggles, try this. Make it a big font and press F9.

=UNICHAR(RANDBETWEEN(0,5)+9856)

​

<strike></strike>

 

[pgc01]

Hi

You can use a string with the valid characters to test your string.

=SUMPRODUCT(--ISERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz0123456789")))>0



"abcd" with test FALSE, there are no special characters
"ab;d" with test TRUE, there is at least one special character (in this case the ";")