Solved

# simplify cleaning up replace function

Posted on 2014-02-26
206 Views
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)
``````
0
Question by:fordraiders

LVL 26

Expert Comment

ID: 39888864
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/
0

LVL 48

Expert Comment

ID: 39888880
Hi,

You could use
``````cD = Replace(Replace(cD, "   ", " "), "  ", " ")
``````
Regards
0

LVL 45

Assisted Solution

Martin Liss earned 100 total points
ID: 39888884
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)
``````
0

LVL 28

Assisted Solution

omgang earned 100 total points
ID: 39888885
Perhaps something like

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

OM Gang
0

LVL 92

Accepted Solution

Patrick Matthews earned 300 total points
ID: 39888921
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.
0

LVL 3

Author Comment

ID: 39889263
patrick, Learn something new every day..
simple solution.
0

## Featured Post

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…