Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-06-19
3
299 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

792 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