Solved

simplify cleaning up replace function

Posted on 2014-02-26
6
206 Views
Last Modified: 2014-02-26
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

0
Comment
Question by:fordraiders
6 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
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

by:Rgonzo1971
ID: 39888880
Hi,

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

Open in new window

Regards
0
 
LVL 45

Assisted Solution

by:Martin Liss
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)

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

Assisted Solution

by:omgang
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

by:
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

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now