I need to create a "IsAlpha" function to check par

TerryK

New Member
Joined
Jul 28, 2006
Messages
18
I'm attempting to write a IsAlpha function that will check if a string variable contains only the characters a-z upper or lower case. Do I have to/can I loop through the alphabetic constant to check each character in turn with a MID or is there some form of search or find VBA keyword that is hidden in the help system and would be quicker?

Thansk in anticipation

Regards

Terry
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Check each character by examining it's Asc value ( see the Asc function help ), which will be in the range 65 to 90 for uppercase, and in the range 97 to 122 for lowercase.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Try
Code:
Function IsAlpha(txt) As Boolean
Dim myTxt
If TypeName(Application.Caller) = "Range" Then
     myTxt = txt.Text
Else
    myTxt = txt
End If
With CreateObject("VBScript.RegExp")
     .Pattern = "[^a-z]"
     .IgnoreCase = True
      IsAlpha = Not .test(myTxt)
End With
End Function
 

Forum statistics

Threads
1,137,296
Messages
5,680,672
Members
419,924
Latest member
Dhamodharan992

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top