HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD

R

Ron Rosenfeld

You could use this UDF:

================
Option Explicit
Function AlphaCount(str As String) As Long
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^A-Za-z]"
AlphaCount = Len(re.Replace(str, ""))
End Function
====================

You enter this in a regular module:

<alt-F11> opens the VBEditor
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code above into the window that opens.

You then enter a formula:

=AlphaCount(cell_ref) in some cell on your worksheet to get a count of the
"alpha" characters in cell_ref.

If you want to include other characters than A-Za-z, just add them at the end
of "pattern"
--ron
 
J

JMB

Ron - is there a decent resource where I could learn more about how to use
VBA regular expressions??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Splitting up address 3
Eliminate wild characters 5
Ron: Edit your code please? 1
Eliminate wild characters 5
How do I count the Characters in a cell 5
Looking for alpha characters in a field 1
How to seperate ALPHANUMERIC data in a cell in an excel sheet. 3
How do I count alpha codes in a range of cells in a row? 1

Top