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

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

Hi Nitin,

Welcome to ExcelFox !!!

one way..

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

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

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)

Quote:

---

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

Quote:

---

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)

Quote:

---

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.

@rick

Dit you consider using offset ?

PHP Code:

---

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


---

Quote:

---

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.

Quote:

---

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

Quote:

---

Originally Posted by NITIN SHETTY

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.