Excel. Count cells with numbers or symbols

Very simple question. I want to create a filter to show only cells with letters exclusively.
I want a function to analyse chars of a cell and return a true if it has numbers or symbols, and a false if ONLY letters (abcdefgijklmnopqrstuvwxyz)
Thanks
Answer #1
help
Answer #2
So, you need a formula which can differentiate between two cases: a) digits and any kind of symbols and b) any text and only text.
While the question is simple, it’s not so easy to find an equally easy answer.
The thing is, that Excel just doesn’t have an exact formula for the kind of information you are looking for. You can check, if a cell is empty or not. You can check, if a cell contains only numbers or not. You can check, if a cell contains anything from a specific single character to a whole word or sentence. All of that is easy. What Excel doesn’t provide is a quick solution to your problem, because you cannot check for specific content of a cell without going way over the top. Excel is perfectly capable of differentiating between numbers and anything else (because it cannot calculate with the “anything else”), but as soon as you need to be more specific than that, it gets complicated. On the first glance, =ISTEXT() could be the solution for you, but this formula will return TRUE whenever the content of a cell is a) not empty and b) not a number, meaning that any kind of symbols in the ascii table will return TRUE as well.
Well, here’s one possible solution, but for that you either need to pack a lot of formulas in your cells or split it up between a number of cells. This is huge work to write, it’s very confusing, it will also slow down Excel considerably and if used a lot, it will certainly bulk up your file size. The following formula is very long, but you get comparatively little in return. It only checks one single cell if it contains text (all characters from a to z, context insensitive) and returns true or false respectively. This formula is working perfectly, but it’s also anything but complete, since it would need to check for each possible ascii character to be truly perfect for your needs. In the end, the following is (an incomplete) proof of concept with built-in Excel-formulas to show you that it can work in theory, but a practical application isn’t reasonable.
=OR(IF(ISERROR(SEARCH("a";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("b";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("c";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("d";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("e";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("f";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("g";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("h";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("j";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("k";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("l";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("m";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("n";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("o";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("p";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("q";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("r";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("s";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("t";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("u";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("v";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("w";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("x";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("y";B4))=TRUE;;TRUE);IF(ISERROR(SEARCH("z";B4))=TRUE;;TRUE))
I’m sure you agree with me that it’s a tad ridiculous, right? That’s why you should go the much easier route and write your own formula in VBA. It sounds terribly complicated, but it’s really not. First you have to activate a reference to work with regular expressions and it’ll become apparent soon why you need them. Press Alt+F11 to go to your code screen, there you need to click -> Tools -> References and select “Microsoft VBScript Regular Expressions 5.5” and confirm this with OK. Now you can use regular expressions in your VBA script and here’s a quick and easy example of what is possible with this. After you added a module (-> Insert -> Module) you can copy the following function into it:
Function ContainsOnlyText(cellText As String) As Boolean
    Dim objRegEx As Object, objMatch As Object
    Set objRegEx = CreateObject("vbscript.regexp")
    With objRegEx
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "^[a-z]+$"
        Set objMatch = .Execute(cellText)
    End With
    If objMatch.Count > 0 Then
        ContainsOnlyText = True
    Else
        ContainsOnlyText = False
    End If
End Function
Wow, you’ve just added your own custom formula to the excel spreadsheet. Great, isn’t it? Now you can type the following into any cell:
=ContainsOnlyText(B4)
This custom formula will check the whole content of the cell B4 for any text (case insensitive characters from a to z) and return true, if that’s the case. If, on the other hand, the cell is either empty or contains anything else like numbers or any symbols in the ascii table – even mixed between regular text – then it will return false. Beware, that the regex, as used in the function/formula above, will only work for alphabetic characters and nothing else. If you’re looking for a working regex with punctuation marks and other often-used characters in regular writing (like . , ; : – _ ! ? ‘), then you’ll need to adapt your regular expression. Use “^[a-z .,;:\-_!?’]+$” instead of “^[a-z]+$” for a pattern and the formula will recognize almost any regularly written text.
That should be perfect for what you require, right? If not, drop a reply with a detailed explanation and I’ll help you sort it out.
Answer #3
Many thanks flash82!
I only saw it now and it worked perfectly!!
I really appreciated your detailed reply
Thanks for you time:D