Replace variable string in VBA

Question

I need to replace somethin in a string, but what to replace may differ. It could be

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

and almost any other combination of spaces and cases above.

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

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

This will not work, obviously. So I need a more sophisticated version of Replace that can handle this task. Is there something I could use, or do I need to write it myself?

Answer 1

If you need more flexibility than using the mj82 method (for example, you may not know the length of the original expression), you can use a regular expression. For instance:

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

Answer 2

This is for an eggyal complimentary answer. This is a VBA regex feature so you can use its answer. You will notice that I added ignore_case as an option for flexibility, so your call will be as follows:

 =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 

Answer 3

If XXX is the first one, you can use the Left (string; 3) function tu cut 3 (or whatever length you need) on the left side, then you don't care what comes after it.