Create custom Replace function

bfuchs used Ask the Experts™
Hi Experts,

I am looking to create a function that will accept a string and only return the alpha/numeric values, except for dashes all special characters should get stripped out.

So instead of having to use the following
fieldName = Left(Replace(Replace(Replace(Mid(ColumnName, 2, Len(ColumnName) - 2), " ", ""), "&", ""), "+", ""), 32)

Open in new window

I can call my function something like

Open in new window

P.S. If possible, would prefer to have the 2nd variable of the function (the one holding the dash) capable of managing more than one character as an exception, so I can send "_,-,' ') and all 3 of them would be considered valid characters, not getting stripped out.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

RegEx might be a good option for this.

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Here's a good article on EE about regex:

 beyond that, there is a lot of generic "find and replace" code floating around.    But this seems specific enough for you to write your own.

 It's simple enough....just loop through the string and dump anything you don't want.   Generally done with an In and out string, the latter of which you build up as you read the "in" string.

Analyst Developer
Distinguished Expert 2018
You could use regex, this one will help you :


Open in new window

You could add as many special characters you want to ignore, that you will pass in the second argument like :

"[^A-Za-z\d" & ingoredChars & "]"

Open in new window

You just need to pass the ingoredChars together like "_-,'" in the second argument.

The whole function code could be something like :

Private Function MyReplace(ByVal strVar As String, ByVal ingoredChars As String) As String
    Set objRegExp_1 = CreateObject("vbscript.regexp")
    objRegExp_1.Global = True
    objRegExp_1.IgnoreCase = True
    objRegExp_1.Pattern = "[^A-Za-z\d" & ingoredChars & "]"
    MsgBox ( objRegExp_1.Replace(strVar, "") )
End Function

Open in new window

And you could call it like :


Open in new window

Not tested yet.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Middle of testing yours...seems to work-:)

Can you explain why do I get into the debug line?
fieldName = Left(Replace(Replace(Replace(Replace(Mid(ColumnName, 2, Len(ColumnName) - 2), " ", ""), "&", ""), "+", ""), "-", ""), 32)
                        If fieldName <> Left(MyReplace(Mid(ColumnName, 2, Len(ColumnName) - 2), "_"), 32) Then
                            Debug.Print fieldName
                        End If

Open in new window

See attached.

Thanks to all participants!
Zakaria AcharkiAnalyst Developer
Distinguished Expert 2018

You're welcome @bfuchs, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial