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

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,
LVL 15
DrTribosAsked:
Who is Participating?
 
Lee W, MVPConnect With a Mentor Technology and Business Process AdvisorCommented:
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
 
DrTribosAuthor Commented:
Hi leew

Thanks for the suggestion.  I thought replace(strTest, "/", "") would only replace the first instance... I will give that a go.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
No, replace replaces ALL instances of a character (or characters) in the string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.