Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

simplify cleaning up replace function

Posted on 2014-02-26
6
Medium Priority
?
215 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 27

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 53

Expert Comment

by:Rgonzo1971
ID: 39888880
Hi,

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

Open in new window

Regards
0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 400 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Assisted Solution

by:omgang
omgang earned 400 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 93

Accepted Solution

by:
Patrick Matthews earned 1200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

916 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