VBA Express : Multiple Apps - Function to Remove either All Numeric or All Non-Numeric Characters from a String

Multiple Apps

Function to Remove either All Numeric or All Non-Numeric Characters from a String

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

matthewspatrick

Description:

This function will remove, at user's option, either all of the numeric characters or all of the non-numeric characters. These simple instructions may be overridden with specific characters that are either always or never allowed.

Discussion:

In the past, I have had to parse alphanumeric strings of varying length, in which the letters meant one thing and the numbers another. I could use formulas to grab just the characters I needed, but the character positions were not always constant, so sometimes the formulas failed. This function would make that sort of task easier: for the given string, the function will either remove all non-numbers or remove all numbers. Further, you can override with certain letters. For example, the optional argument AllowedChar allows you to specify a string whose characters are always allowed. Thus, you could specify "$,." and allow the dollar sign, comma, and period to pass unmolested even if the function is told to remove non-numeric characters. The optional argument NeverAllow also sets up an override, this time of characters never allowed into the result string.

Code:

instructions for use

Option Explicit Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _ Optional AllowedChar As String, Optional NeverAllow As String) ' For the given string, the function removes all numeric characters (KillNumbers=True) or ' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override ' characters that are always allowed. For example, "$,." would indicate that the dollar sign, ' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate ' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override ' characters that are never allowed. The "never allowed" characters are processed before the ' "always allowed" characters, and so if any characters are in both strings Never allow takes ' precedence ' The AllowedChar and NeverAllow arguments are *not* case-sensitive Dim Counter As Long Dim TestChar As String Dim TestAsc As Long ' Loop through characters For Counter = 1 To Len(CheckStr) ' Get current character and its ANSI number TestChar = Mid(CheckStr, Counter, 1) TestAsc = Asc(TestChar) ' Test first to see if current character is never allowed If InStr(1, NeverAllow, TestChar, vbTextCompare) > 0 Then ' do nothing ' If current character is in AllowedChar, keep it ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) > 0 Then StripOutCharType = StripOutCharType & TestChar ' If KillNumbers=True, test for not being in numeric range for ANSI ElseIf KillNumbers Then 'only allow non-numbers If TestAsc < 48 Or TestAsc > 57 Then StripOutCharType = StripOutCharType & TestChar End If ' If KillNumbers=False, test for being in numeric ANSI range Else 'only allow numbers If TestAsc >= 48 And TestAsc <= 57 Then StripOutCharType = StripOutCharType & TestChar End If End If Next End Function

How to use:

Paste the code above into a regular module in your project
Use the function in your code, or in your application's user interface (such as in an Excel worksheet formula--see the attached file for examples--or in an Access query, form, or report)

Test the code:

Try several iterations with the function, varying the argument settings.
Please see the attached sample file for several examples of how to use this function.

Sample File:

StripOutCharTypeExample.zip 8.58KB

Approved by mdmackillop

This entry has been viewed 138 times.