Replace variable string in VBA

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 ot开发者_如何学Pythonher 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?

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)

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

If XXX is first, you can use Left(string; 3) function tu cut 3 (or any length you need) letters from left side, then you don't care what's after it.