asked on

Using regular expressions to remove characters from a cell

Hi

Cell A1 has text " **1.0 " I want to use this regular expression \*\*\d.\d to remove the **1.0 is there a function that will allow me to do that.

Something like =removematch(A1,\*\*\d.\d)

Thanks you.

Patrick Matthews

Try this:

Function RemoveMatch(LookIn, PatternStr)

Dim re As Object

Set re = CreateObject("VBScript.RegExp")
With re
.Pattern = PatternStr
.Global = True
End With

RemoveMatch = re.Replace(LookIn, "")

Set re = Nothing

End Function

Patrick

Patrick Matthews

Better:

Function RemoveMatch(LookIn, PatternStr, Optional ReplaceWith = "")

Dim re As Object

Set re = CreateObject("VBScript.RegExp")
With re
.Pattern = PatternStr
.Global = True
End With

RemoveMatch = re.Replace(LookIn, ReplaceWith)

Set re = Nothing

End Function

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

hi guys

the . is a single character wild card that should be precede with \

a pattern that removes
1 to x * .... 1 to x digits ... dot 1 to x digits is
=RemoveMatch(A1,"\*+\d+\.\d+")

which leaves a single blank character on your test string

Cheers

Dave

Patrick Matthews

Dave,

Thanks for the comment on the pattern string. Still getting the hang of it :)

Regardless of how to build the pattern string, though, the UDF is doing the right thing, right?

Wondering when the RegExp Posse would show up,

Patrick

Dave

Hi Patrick,

Works fine :)

Although the
.Global = True
is redundant if only a one -off replacement is needed

Dave

profess

ASKER

Hi,

I must be doing something wrong I keep getting the name? error. Its been a long time since I have used VBA. I should be able to just copy the function in the VBA editor for the sheet I am working on right?

Galen

Patrick Matthews

Galen,

No, for functions to be available in the Excel UI they have to be in a regular module, not an object module.

Patrick