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?

RegEx(see this) may work for you. I'm not sure what your pattern string would look like, but there's lots of sites for writing those. — jonsca
– jonsca, Commented May 4, 2011 at 13:02

eggyal · Accepted Answer · 2011-05-04 13:24:41Z

8

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)

answered May 4, 2011 at 13:24

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Merchant Over a year ago

I'm trying to use your function and am getting an object doesn't exist error. When I add a watch for "Regex" it says that the object is empty. Any ideas why this is happening? Do I need to load a library? I'm in Excel 2010

Toast Over a year ago

You need to add Microsoft VBScript Regular Expressions to your references (current version is I think 5.5), then declare the variable using: Dim Regex as New Regexp.

Gaijinhunter · Accepted Answer · 2013-02-28 14:44:20Z

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

mj82 · Accepted Answer · 2011-05-04 13:01:22Z

1

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.

answered May 4, 2011 at 13:01

mj82

5,2637 gold badges34 silver badges39 bronze badges

2 Comments

F.P Over a year ago

Okay, I forgot to clarify that in the question. XXX is also variable of any character (maybe even including "test" and alike)

mj82 Over a year ago

If you can cut until "-" sign (and it cannot be in XXX expression), then this may help: Left(mystr, InStr(mystr, "-") - 1), it cuts all the letters until first occurance of "-". Otherwise - Regex is best solution.

Collectives™ on Stack Overflow

Replace variable string in VBA

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related