Solved

simplify cleaning up replace function

Posted on 2014-02-26
6
208 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 49

Expert Comment

by:Rgonzo1971
ID: 39888880
Hi,

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

Open in new window

Regards
0
 
LVL 46

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 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