Excel Formula To Check If All Characters Are Text Non-Numeric

[NITIN SHETTY]

Hello,

I need a formula to check whether all the letters in a cell are text for Ex "ABDJKJKD"

It should result true if all the letters are text and false if there is any special character or number for Ex "ABDJ7JKD

Please help

[Admin]

Hi Nitin,

Welcome to ExcelFox !!!

one way..

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1)

[NITIN SHETTY]

Hi,


Thanks for your help it does work for numbers for Ex ABCD43D

But not in case of special characters For Ex ABCD#$D

Please help.


Thanks

NItin shetty

[Admin]

My bad, didn't consider the special characters :(

try

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)

[NITIN SHETTY]

My bad, didn't consider the special characters :(

try

=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)


Tried the Above formula but it is not working

Thanks

Nitin Shetty



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

Hello,

I need a formula to check whether all the letters in a cell are text for Ex "ABDJKJKD"

It should result true if all the letters are text and false if there is any special character or number for Ex "ABDJ7JKD

Please help

This is easy to do with a UDF (user defined function) if a VBA solution is acceptable to you.

Function IsLetters(S As String) As Boolean
IsLetters = Not S Like "*[!A-Za-z]*"
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IsLetters just like it was a built-in Excel function. For example,

=IsLetters(A1)

[Rick Rothstein]

This is easy to do with a UDF (user defined function) if a VBA solution is acceptable to you.

Function IsLetters(S As String) As Boolean
IsLetters = Not S Like "*[!A-Za-z]*"
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IsLetters just like it was a built-in Excel function. For example,

=IsLetters(A1)
However, if you want a formula solution, then this array-entered** formula will return TRUE if the text is all letters and FALSE otherwise...


=MAX(ABS(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))-77.5))<13
**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.

[snb]

@rick

Dit you consider using offset ?


=MAX(ABS(CODE(MID(UPPER(A1),ROW(OFFSET(A1,,,LEN(A1 ))),1))-77.5))<13

[Rick Rothstein]

@rick

Did you consider using offset ?


=MAX(ABS(CODE(MID(UPPER(A1),ROW(OFFSET(A1,,,LEN(A1 ))),1))-77.5))<13
I do not see what the benefit of using OFFSET has over using INDIRECT... your formula, like mine, still needs to be array-entered and it is still Volatile. Although I find the construction awkward, the following modification to my formula has the benefit of being non-Volatile (still needs to be array-entered** though)...

=MAX(ABS(CODE(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX (A:A,LEN(A1))),1))-77.5))<13

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.

[NITIN SHETTY]

I do not see what the benefit of using OFFSET has over using INDIRECT... your formula, like mine, still needs to be array-entered and it is still Volatile. Although I find the construction awkward, the following modification to my formula has the benefit of being non-Volatile (still needs to be array-entered** though)...

=MAX(ABS(CODE(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX (A:A,LEN(A1))),1))-77.5))<13

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.

Hi Rick,

Thanks for helping but it is not working for special characters say like abc.. der

Thanks

Nitin

[Rick Rothstein]

Hi Rick,

Thanks for helping but it is not working for special characters say like abc.. der

That is probably because you did not follow my instructions about array-entering** the formula...

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself.