isAlpha returning true on non alpha character

[brickbuilder]

Trying to clean up a number that's always written out. The problem is isAlpha is either not being called or is returning true on certain type of spacing...not sure if it's return or whitespace or tab....I looked at the ascii chart and isAlpha seems to be bounded correctly. Confused!

phoneNum = Mid(replyText, pos + Len(phoneDelim))
        For i = 1 To Len(phoneNum)
            If isAlpha(Mid(phoneNum, i, 1)) = True Then
                phoneNum = Mid(phoneNum, i)
                phoneNum = Left(phoneNum, InStr(phoneNum, vbLf) - 1)
                Exit For
            End If
        Next i

isAlpha

Function isAlpha(str As String) As Boolean
'checks whether the string is completely alphabetic
    Dim Flag As Boolean
    Dim s As String
    Flag = True
     
    For i = 1 To Len(Trim(str))
        s = Asc(Mid(Trim(str), i, 1))
        If s > 64 And s < 91 Or s > 96 And s < 123 Then 'space counts as alpha '32'
            'do nothing
        Else
            Flag = False
        End If
    Next i
     
    If Flag = True Then
        isAlpha = True
    Else
        isAlpha = False
    End If
End Function

 

[AlphaFrog]

Try replacing this...
If isAlpha(Mid(phoneNum, i, 1)) = True Then

With This...
If UCase(Mid(phoneNum, i, 1)) Like "[A-Z]" Then

 

[Rick Rothstein]

Your IsAlpha function does not need to be anywhere near that complex, try this one instead...

Function IsAlpha(S As String) As Boolean
  IsAlpha = Not S Like "*[!A-Za-z ]*"
End Function

You can probably simplify the phone number code, but quite frankly, I am unsure what it is supposed to be doing. Can you explain what that code is attempting to do?

 

[brickbuilder]

Thank you!
I was just surprised that there's a built in IsNumber without a matching IsAlpha to go with it. First time seeing the Like keyword too. Thanks I'll be looking that one up.

 

[AlphaFrog]

Thank you!
I was just surprised that there's a built in IsNumber without a matching IsAlpha to go with it. First time seeing the Like keyword too. Thanks I'll be looking that one up.

You're welcome.

If you think about it, IsNumber tests for a numeric data type and not a character subset. It will evaluate to True for numbers, fractions, scientific notation, dates. It doesn't just detect characters 0-9.

An IsText function would be ambiguous. Everything is text really including numeric characters and many symbols. Perhaps an IsAlphabet function may be useful or IsA2Z. But then there are international characters to consider.

 

[brickbuilder]

IsAlphabet is what I was thinking of. Maybe IsAlphabetOrNumerical too. International characters would throw a monkey wrench into the idea though

 

[Rick Rothstein]

IsAlphabet is what I was thinking of. Maybe IsAlphabetOrNumerical too.

That is what VBA is for... to be able to create routines to do the things the Excel does not do natively. Your IsAlphabet function is what I posted earlier, but here is a more flexible version as it. By default, the function will only validate alphabetic letters, but if you want to include other characters such as a space, digits (use 0-9 to include them), a dash, an accented é, etc., just pass them into the optional second argument as a quoted text string (don't forget to include both upper and lower case characters if both are needed and always make the dash the last character, if included, so it does not accidentally get recognized as a character range indicator)...

Function IsAlpha(S As String, Optional ExtraChars As String) As Boolean
  IsAlpha = Not S Like "*[!A-Za-z" & ExtraChars & "]*"
End Function

So, for example, if you wanted to test a text string for being any upper or lower case letter, a space, a dash, any digit from 1 to 7 or an accented é, you would check it like this...

If IsAlpha(YourTextString, "1-7é -") Then

That should give you a pretty decent text verification ability in Excel.

 

[brickbuilder]

Thanks a lot Rick. That will be a useful little function to keep in a background module. Cheers!