simplify cleaning up replace function

Fordraiders
Fordraiders used Ask the Experts™
on
excel 2010 vba
userform
textbox

The code below works ok and i'm sure can be simplified , but
If a special character(s) are inside the string.
Eventually the string will become padded with <spaces>

Example:

16oz# claw hammer ! special$ amount

becomes:

16oz<space><space>claw <space>hammer<space><space><space>special<space><space>amount

it needs to be :
16oz claw hammer special amount


Thanks
fordraiders


Dim cD As String
cD = UserForm2.TextBox33

' strip special characters first
cD = Replace(cD, "#", " ")
cD = Replace(cD, "!", " ")
cD = Replace(cD, "@", " ")
cD = Replace(cD, "$", " ")
cD = Replace(cD, "^", " ")

cD = Trim(cD)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun KlineLead Software Engineer

Commented:
Consider using regular expressions to perform this cleanup. Here is a tutorial:
http://www.macrostash.com/2011/10/08/simple-regular-expression-tutorial-for-excel-vba/
Top Expert 2016

Commented:
Hi,

You could use
cD = Replace(Replace(cD, "   ", " "), "  ", " ")

Open in new window

Regards
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Replace with no space rather than a space, like this.

Dim cD As String
cD = UserForm2.TextBox33

' strip special characters first
cD = Replace(cD, "#", "")
cD = Replace(cD, "!", "")
cD = Replace(cD, "@", "")
cD = Replace(cD, "$", "")
cD = Replace(cD, "^", "")

cD = Trim(cD)

Open in new window

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

omgangIT Manager
Commented:
Perhaps something like

If InStr("# ") <> 0 Or InStr(" #") <> 0 Then
    cD = Replace(cD, "#", "")
ElseIf InStr(" #  ") <> 0 Then
    cD = Replace(cD, " #", "")
End If

OM Gang
Top Expert 2010
Commented:
Use Excel's version of Trim, rather than VBA's version of Trim (yes, they are different):

cD = Application.Trim(cD)

Excel's version removes "extra" spaces in the middle of a string.  VBA's version only removes leading and trailing spaces.

Author

Commented:
patrick, Learn something new every day..
simple solution.

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