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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,722
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,170,931
Messages
5,872,774
Members
432,944
Latest member
mj02

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