-2

I cant seem to figure out how to put this all into regex. I know there has got to be an easy way but i am stumped. I dont think you can do chaining with vba excel? Any guidance would be awesome. Thanks!!

Set Myrange = ActiveSheet.Range(Range("A2"), Range("A2").End(xlDown))

For Each Cell In Myrange
        strInput = LCase(Cell.value)
        strInput = Replace(strInput, " ", "-")
        strInput = Replace(strInput, ".", "-")
        strInput = Replace(strInput, ",", "-")
        strInput = Replace(strInput, "'", "")
        strInput = Replace(strInput, ")", "")
        strInput = Replace(strInput, "(", "")
        strInput = Replace(strInput, "---", "-")
        strInput = Replace(strInput, "--", "-")
        strInput = Replace(strInput, "®", "")
        strInput = Replace(strInput, "™", "")
        strInput = Replace(strInput, "%", "-percent")
        strInput = Replace(strInput, """", "-inch")

        Cells(Cell.Row, "F") = strInput
Next
Community
  • 1
  • 1
Wendy Medeiros
  • 9
  • 1
  • 3
  • 1
    What does this have to do with regex? You've dumped a bunch of string replace code, not explained a problem, there's absolutely no regular expression anywhere in your question... What's wrong with the code you currently have? – Ken White Sep 26 '17 at 17:39
  • You have various replacement strings, you can't merge it into 1 regex replace code. – Wiktor Stribiżew Sep 26 '17 at 17:50
  • Thanks, that was the question. I knew how to do it with replace strings, it’s the regex that I was trying to convert it to. Regex confuses me lol. For some reason I can’t seem to grasp it – Wendy Medeiros Sep 27 '17 at 03:05

1 Answers1

0

Brief

You require 4 regular expressions according to the information you've given.
See this post for information on how to run regex in VBA

Code

Expression 1

Regex

([ .,]|-{2,3})

Replacement

-

Expression 2

Regex

(['()]|®|™)

Replacement

Expression 3

Regex

(%)

Replacement

-percent

Expression 4

Regex

(")

Replacement

-inch
ctwheels
  • 21,901
  • 9
  • 42
  • 77