# isAlpha returning true on non alpha character

#### brickbuilder

##### New Member
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!

Code:
``````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
Code:
``````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``````

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try replacing this...
If isAlpha(Mid(phoneNum, i, 1)) = True Then

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

Your IsAlpha function does not need to be anywhere near that complex, try this one instead...
Code:
``````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?

Last edited:
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.

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.

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

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)...

Code:
``````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...

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

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

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

Replies
7
Views
447
Replies
2
Views
387
Replies
2
Views
164
Replies
3
Views
265
Replies
0
Views
216

1,206,945
Messages
6,075,782
Members
446,156
Latest member
Aaro23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back