You don't need RegEx for what you're doing.
No. But she doesn't *need* anything beyond what Excel worksheet functions
provide. As written in her example,
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Jim's Roofing...""The best roofing
in town!""","'",""),".",""),"""",""),"!","")
would suffice. As for your example,
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Hello Mommy","e","a"),"l","b"),"m","")
would suffice. Granted nested SUBSTITUTE calls don't nest well, but if there are
8 or fewer characters to replace, nested SUBSTITUTE calls would be faster than
udfs.
I suspect the OP's using an overly simplified example. I'd guess only letters
and whitespace should remain. While it'd be possible to feed your UDF a string
of all the other characters as 2nd argument and "" as 3rd argument, that's be
much more difficult than deleting all characters in the class [^A-Za-z \t].
Regexps may not be necessary, strictly speaking, but they wipe the floor with
all other text matching approaches in terms of economy of expression.
A udf is also required to make use of the RegExp object provided by VBScript. If
the OP has Internet Explorer 5.0 or later installed, then VBScript 5 or higher
was installed with it. If not, it's a free download from Microsoft's web site.
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp
Once installed, the udf below could be used in a formula like
=Subst("Jim's Roofing...""The best roofing in town!""","[^A-Za-z \t]+","")
Note that this doesn't give a space between the words Roofing and The. To
replace multiple chars with one space and delete single chars, try
=Subst(Subst("Jim's Roofing...""The best roofing in town!""",
"[^A-Za-z \t]{2,}"," "),"[^A-Za-z \t]","")
This wrinkle would pose nontrivial problems using MultiSubstitute if *single*
unwanted chars should be deleted while *multiple* unwanted chars should be
replaced by a single space. There's a place for transliteration, but it's not a
replacement for regular expressions.
'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps
'Required:
' orig_text - string to search through,
' match_pat - regexp to find,
' replace_pat - replacement pattern
'Optional:
' instance - which matched substring to replace or 0 for all (default)
'-----------------------------------------
Function Subst(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant) As Variant
'-----------------------------------------
Dim regex As Object, matches As Object, m As Object
If IsMissing(instance) Then
instance = 0#
ElseIf TypeName(instance) <> "Double" Then
Subst = CVErr(xlErrValue) 'invalid instance type
instance = -1#
ElseIf CDbl(instance) <= 0.5 Then
Subst = CVErr(xlErrNum) 'invalid instance value
instance = -1#
Else
instance = Int(instance + 0.5)
End If
If instance = -1# Then Exit Function 'do nothing quickly
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True
If instance = 0# Then
Subst = regex.Replace(orig_text, replace_pat)
Else
Set matches = regex.Execute(orig_text)
If instance > matches.Count Then
Subst = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Subst = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
End If
End Function
