How to write a regular expression that includes numbers [0-9] and a defined word?

Question

I'm using VBA and struggling to make a regex.replace function to clean my string cells

Example: "Foo World 4563" What I want: "World"

by replacing the numbers and the word "Foo"

Another example: "Hello World 435 Foo", I want "Hello World"

This is what my code looks like so far:

Public Function Replacement(sInput) As String

Dim regex As New RegExp

With regex
   .Global = True
   .IgnoreCase = True
End With

regex.Pattern = "[0-9,()/-]+\bfoo\b"
Replacement = regex.Replace(sInput, "")

End Function

I'm confused, you wanna replace Foo and 4563? – 0m3r Oct 11 '22 at 20:42 — 0m3r, Oct 11 '22 at 20:42

score 1 · Answer 1 · answered Oct 11 '22 at 20:49

You can use

Function Replacement(sInput) As String

Dim regex As New regExp

With regex
   .Global = True
   .IgnoreCase = True
End With

regex.Pattern = "\s*(?:\bfoo\b|\d+)"
Replacement = Trim(regex.Replace(sInput, ""))

End Function

See the regex demo. Excel test:

Details:

\s* - zero or more whitespaces
(?:\bfoo\b|\d+) - either a whole word foo or one or more digits.

Note the use of Trim(), it is necessary to remove leading/trailing spaces that may remain after the replacement.

score 0 · Answer 2 · answered Oct 11 '22 at 21:05

My two cents, capturing preceding whitespace chars when present trying to prevent possible false positives:

(^|\s+)(?:foo|\d+)(?=\s+|$)

See an online demo.

(^|\s+) - 1st Capture group to assert position is preceded by whitespace or directly at start of string;
(?:foo|\d+) - Non-capture group with the alternation between digits or 'foo';
(?=\s+|$) - Positive lookahead to assert position is followed by whitespace or end-line anchor.

Sub Test()

Dim arr As Variant: arr = Array("Foo World 4563", "Hello World 435 Foo", "There is a 99% chance of false positives which is foo-bar!")

For Each el In arr
    Debug.Print Replacement(el)
Next

End Sub

Public Function Replacement(sInput) As String

With CreateObject("vbscript.regexp")
   .Global = True
   .IgnoreCase = True
   .Pattern = "(^|\s+)(?:foo|\d+)(?=\s+|$)"
   Replacement = Application.Trim(.Replace(sInput, "$1"))
End With

End Function

Print:

World
Hello World
There is a 99% chance of false positives which is foo-bar!

Here Application.Trim() does take care of multiple whitespace chars left inside your string.

How to write a regular expression that includes numbers [0-9] and a defined word?

2 Answers2