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

**NITIN SHETTY** · 12-09-2012, 02:43 PM

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** · 12-09-2012, 03:06 PM

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** · 12-09-2012, 03:19 PM

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** · 12-09-2012, 04:46 PM

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** · 12-09-2012, 11:13 PM

Originally Posted by 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)

Tried the Above formula but it is not working

Thanks

Nitin Shetty

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

**Rick Rothstein** · 12-09-2012, 11:50 PM

Originally Posted by 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

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

Code:

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** · 12-10-2012, 01:43 AM

Originally Posted by Rick Rothstein

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

Code:

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...

Code:

=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** · 12-10-2012, 02:53 AM

@rick

Dit you consider using offset ?

PHP Code:


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

**Rick Rothstein** · 12-10-2012, 08:56 AM

Originally Posted by snb

@rick

Did you consider using offset ?

PHP Code:


=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** · 12-10-2012, 11:11 PM

Originally Posted by Rick Rothstein

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