Solved

Replacing Characters - A more elegant way to do this in VBA?

Posted on 2013-06-19
3
293 Views
Last Modified: 2013-06-19
Hi All

I have a block of code that cleans up a string.  It uses 4 Do Loops, looking at it I just can't help but cringe - is there a better way to do this?
        Do While InStr(1, strTest, "/")
          strTest = Replace(strTest, "/", "")
        Loop
        Do While InStr(1, strTest, " ")
          strTest = Replace(strTest, " ", "")
        Loop
        Do While InStr(1, strTest, "-")
          strTest = Replace(strTest, "-", "")
        Loop
        Do While InStr(1, strTest, ",")
          strTest = Replace(strTest, ",", "")
        Loop

Open in new window

Kind regards,
0
Comment
Question by:DrTribos
  • 2
3 Comments
 
LVL 95

Accepted Solution

by:
Lee W, MVP earned 500 total points
ID: 39261331
Drop the loops.  They seem pointless to me.  The way they read, you're checking to see if the string contains the character.  If you just execute the replace, it should work the same way.

Indeed, you COULD combine it like this:

strTest = Replace(Replace(Replace(Replace(strTest, "/", ""), " ", ""), "-", ""), ",", "")
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39261347
Hi leew

Thanks for the suggestion.  I thought replace(strTest, "/", "") would only replace the first instance... I will give that a go.
0
 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 39261350
No, replace replaces ALL instances of a character (or characters) in the string.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

821 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