Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Nitin,

    Welcome to ExcelFox !!!

    one way..

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

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0

    TO CHECK IF ALL THE CHARACTERS ARE TEXT

    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

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by Admin View Post
    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
    Last edited by DocAElstein; 06-11-2023 at 01:18 PM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by NITIN SHETTY View Post
    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)

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    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.
    Last edited by Rick Rothstein; 12-10-2012 at 01:46 AM.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @rick

    Dit you consider using offset ?

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

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    @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.
    Last edited by Rick Rothstein; 12-10-2012 at 09:17 AM.

  10. #10
    Junior Member
    Join Date
    Dec 2012
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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

Similar Threads

  1. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  2. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  3. Replies: 11
    Last Post: 04-07-2013, 07:51 PM
  4. Macro to check values based on certain text
    By Howardc in forum Excel Help
    Replies: 25
    Last Post: 11-05-2012, 09:03 PM
  5. Replies: 6
    Last Post: 09-26-2011, 07:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •