Replace variable string in VBA

Question

I need to replace somethin in a string, but what to replace may vary. It can be

XY - test
XXxY-test
XXyyXx-TEST
yXyy     -Test

and virtually any other combination of whitespaces and cases of the above.

I need to replace the "-test" part and leave the "XXX" alone. So, when using a simple replace

Replace("XXX  -test", "- test", "")

It won't work, obviously. So I need a more sophisticated version of Replace that could handle this task. Is there something I could use or do I have to write it on my own?

Answer 1

If you need more flexibility than that provided by mj82's method (for example, you may not know the length of the initial expression), you can use a regular expression. For example:

Regex.Replace("XXX  -test", "\s*-\s*test", "", RegexOptions.IgnoreCase)

Answer 2

This is to compliment eggyal's answer. This is a VBA regexreplace function so that you can use his answer. You'll notice I added ignore_case as an option for flexibility, so your call would be:

=RegexReplace(cell, "\s*-\s*test", "", TRUE)

Here is the function, I hope you find it useful:

' -------------------------------------------------------------------
' Search and Replace using a regular expression
' -------------------------------------------------------------------

Function RegexReplace(ByVal text As String, _
                      ByVal replace_what As String, _
                      ByVal replace_with As String, _
                      Optional ByVal ignore_case As Boolean = False) As String

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.ignorecase = ignore_case
RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)

End Function