Multiple Apps

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

Ease of Use


Version tested with

2000, 2002 

Submitted by:



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. 


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. 


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:

  1. Paste the code above into a regular module in your project
  2. 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:

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

Sample File: 8.58KB 

Approved by mdmackillop

This entry has been viewed 156 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express