Function to return TRUE if cell only has Pure Alpha

Question

I am trying to create a function which identify only the Pure Alpha characters and ignore the Special characters.

And similar to this looking for one more function that is Pure Numeric characters ignoring the special character.

But my code is not doing what i am looking for. Your help will be much appreciated.

Public Function IsLetters(s As String) As Boolean
    Dim i As Long

    IsLetters = False
    For i = 1 To Len(s)
        If Not Mid(s, i, 1) Like "[a-zA-Z]" Then Exit Function
    Next i
    IsLetters = True
End Function

That's not [how you use RegEx in Excel VBA](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops)… — Chronocidal, Jan 22 '22 at 22:56
Your Like pattern passes _only_ alphabetic characters, but your samples pass spaces and (some) punctuation characters too. Update your like pattern to pass _all_ acceptable characters — chris neilsen, Jan 22 '22 at 23:13
Maybe you mean `If Mid(s, i, 1) Like "[0-9]" Then Exit Function`, but then you would want to call it `IsNotDigits`. — VBasic2008, Jan 22 '22 at 23:52
Almost all your items at the example starts with a dot, so it seems that dot is an acceptable character. Alpha is not including [SPACE] as character... Should it be included? With it, then you should modify your "like" statement to: "[A-Z.a-z ]", so you will include dot and space. — David García Bodego, Jan 23 '22 at 00:15
@DavidGarcíaBodego, some of the samples include `,` or `-`. Without clarity from the OP, any suggested pattern is only speculation — chris neilsen, Jan 23 '22 at 00:19
@chris neilsen, it was just an example. It seems that OP want to check something more that special characters. In fact, he/she should explaing which items are returned a not expected value. — David García Bodego, Jan 23 '22 at 00:26
@DavidGarcíaBodego so we're agreed, the Q needs detail or clarity. — chris neilsen, Jan 23 '22 at 00:29
@chris neilsen i was looking for the pattern that i was unable to get thourgh Google. But thank you. — , Jan 23 '22 at 09:34

VBasic2008 · Accepted Answer · 2022-01-23T00:02:14.540

No Digits vs No Letters

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether none of the characters
'               of a string are digits.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsNotDigits( _
    ByVal SearchString As String, _
    Optional ByVal IncludeNullString As Boolean = False) _
As Boolean
    
    If Len(SearchString) > 0 Then
        Dim n As Long
        For n = 1 To Len(SearchString)
            If Mid(SearchString, n, 1) Like "[0-9]" Then Exit Function
        Next n
        IsNotDigits = True
    Else
        If IncludeNullString Then IsNotDigits = True
    End If

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a boolean indicating whether none of the characters
'               of a string are letters.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IsNotLetters( _
    ByVal SearchString As String, _
    Optional ByVal IncludeNullString As Boolean = False) _
As Boolean
    
    If Len(SearchString) > 0 Then
        Dim n As Long
        For n = 1 To Len(SearchString)
            If Mid(SearchString, n, 1) Like "[A-Za-z]" Then Exit Function
        Next n
        IsNotLetters = True
    Else
        If IncludeNullString Then IsNotLetters = True
    End If

End Function

Thank you very much its working unexpectedly. @VBasic2008 – Jan 23 '22 at 09:33 — , Jan 23 '22 at 09:33

Function to return TRUE if cell only has Pure Alpha

1 Answers1

No Digits vs No Letters