CODEWITHSUNDEEP
vbastringexcelreplace
F.P
F.P

Reputation: 17831

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 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?

Upvotes: 6

Views: 18544

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

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

Upvotes: 8

eggyal
eggyal

Reputation: 126015

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)

Upvotes: 8

mj82
mj82

Reputation: 5263

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.

Upvotes: 1

Related Questions