3

I am working in VBA and have a cell with all the letters of the alphabet, and I want to replace all the letters (with nothing, "", or a numeric, "0"), as well as possible punctuation. I currently run it as:

val = Replace(val, "(", "")
val = Replace(val, "]", "")
val = Replace(val, "A", "")
val = Replace(val, "B", "")
val = Replace(val, "C", "")
val = Replace(val, "D", "")
val = Replace(val, "E", "")
val = Replace(val, "F", "")
val = Replace(val, "G", "")
val = Replace(val, "H", "")
val = Replace(val, "I", "")
val = Replace(val, "J", "")
val = Replace(val, "K", "")
val = Replace(val, "L", "")
val = Replace(val, "M", "")
val = Replace(val, "N", "")
val = Replace(val, "O", "")
val = Replace(val, "P", "")
val = Replace(val, "Q", "")
val = Replace(val, "R", "")
val = Replace(val, "S", "")
val = Replace(val, "T", "")
val = Replace(val, "V", "")
val = Replace(val, "W", "")
val = Replace(val, "X", "")
val = Replace(val, "Y", "")
val = Replace(val, "Z", "")

I tried:

val = Replace(val, "(", "")
val = Replace(val, "]", "")
val = Replace(val, "[A-TV-Z]", "")

I could add a loop, but then the computation time will not change.

But it does not work. Is there a way to replace the 25 lines of code with a single line, as I tried, as I have to read through 1M cells, and it takes a long time to run currently. The sad part is, this replacement is run on separate occasions throughout.

frank
  • 3,036
  • 7
  • 33
  • 65

2 Answers2

2

The Regex.Replace() function is required to use regular expressions:

val = Replace(val, "(", "")
val = Replace(val, "]", "")
val = Regex.Replace(val, "[a-z]", "", RegexOptions.IgnoreCase)
Chris Hamilton
  • 555
  • 5
  • 22
2

You can try using regex. I can't get this on single line but it should be quicker than your current version and has fewer lines. Note you'll need a reference to the Microsoft VBScript Regular Expressions library.

Public Function Replacement(sInput) As String

Dim regex As New RegExp

With regex
   .Global = True
   .IgnoreCase = False
End With

regex.pattern = "[A-Z, a-z]"
Replacement = regex.Replace(sInput, "")


End Function
RyanL
  • 1,246
  • 2
  • 10
  • 14
  • this worked, I just needed to add functionality from the VBA board. Tools  references and checked both Microsoft VBScript Regular Expressions 1.0 Microsoft and VBScript Regular Expressions 5.5 – frank Feb 14 '17 at 10:59
  • @alex just one of the two references is needed, or you can use late binding instead http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function – Slai Feb 14 '17 at 20:31