We help IT Professionals succeed at work.

Create custom Replace function

bfuchs
bfuchs asked
on
100 Views
1 Endorsement
Last Modified: 2018-12-28
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
MyReplace(sTrVar,"_")

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.

Thanks
Comment
Watch Question

Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
RegEx might be a good option for this.

See: https://stackoverflow.com/a/43166192/9706001
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Here's a good article on EE about regex:

https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

 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.

Jim.
Analyst Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
@Zakaria,

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,
Ben
Untitled.png
CERTIFIED EXPERT

Author

Commented:
Thanks to all participants!
Zakaria AcharkiAnalyst Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're welcome @bfuchs, glad to help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions