regEx replace in Excel with VB

Question

Hello All, I really dislike making Excel Macro's :-(

Here is the typical string I am working with:

·         Identify & document site-related constraints and assumptions.

I would like to scrub that string to get rid of everything before "Identify"...

I wrote a function to take the string and scrub it, here it is:

Function dataScrub(dataIn As String)
Dim dataIn_orig As String
dataIn_orig = dataIn

'BEGIN : create and set regular expression
Dim regEx
Set regEx = CreateObject("vbscript.regexp")
With regEx
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "^[\s]*[·]+[\s]*"
        .Global = True
End With

dataScrub = regEx.Replace(dataIn_orig, "")
End Function

For an unknown reason, the replace is replacing the · (not a period, more like a bullet) but not getting rid of the spaces that follow it, so my end result is:

      Identify & document site-related constraints and assumptions.

When I test my regEx using an online tester (http://www.regular-expressions.info/javascriptexample.html), it works as intended.

Any help would be greatly appreciated!

Answer 1

[\s] matches either a \ or an s. Try using just plain old \s without the square braces.

---

Well, since that still doesn't work, the last \s* might be a reluctant match instead of a greedy one. You should be able to fix that by adding a \b to the end of your expression. \b indicates a word boundary (either before or after a word). Give this one a try:

^\s*[·]+\s*\b

Answer 2

Looks like the answer has been accepted already, but running this line of code in your function will take care of you issue as well.

Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart