HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD
 

HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD
 

On Wed, 29 Aug 2007 14:50:01 -0700, PVSPRO
wrote:

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

HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD
 

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


"Ron Rosenfeld" wrote:

On Wed, 29 Aug 2007 14:50:01 -0700, PVSPRO
wrote:

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

HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD
 

On Wed, 29 Aug 2007 22:56:00 -0700, JMB wrote:

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

I've found these quite helpful as a beginning.


http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://www.regular-expressions.info/reference.html
--ron

HOW DO YOU COUNT ALPHA CHARACTERS IN AN ALPHANUMERIC FIELD
 

Thanks!!

"Ron Rosenfeld" wrote:

On Wed, 29 Aug 2007 22:56:00 -0700, JMB wrote:

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

I've found these quite helpful as a beginning.


http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://www.regular-expressions.info/reference.html
--ron