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

x
?
Solved

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

Posted on 2013-06-19
3
Medium Priority
?
328 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 96

Accepted Solution

by:
Lee W, MVP earned 2000 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 96

Expert Comment

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
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…

927 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