Solved

simplify cleaning up replace function

Posted on 2014-02-26
6
209 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 50

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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

828 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