Create custom Replace function
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)
MyReplace(sTrVar,"_")
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
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.
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
See attached.
Thanks,
Ben
Untitled.png
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
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