Regex in vba find word position

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have this:
ModelRangeFound: InStr([mODEL RANGE],[forms]![Form1].[word].[value])>0

It fails as it picks up the search word anywhere.

I was thinking of using regex to do the search term but how do I tell it to use the found word if its the first or second word only?

"RG125 bike year a6"  fails as it finds as a6 at end
"A6 quattro s line" This is valid

How do I get regex to find the search string but only return true if the search word is at the beginning of the sentence?

I dont know how to make that regex pattern
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How about something like this where YOUR_SENTENCE is a sentence.

If Split(YOUR_SENTENCE, " ")(0) = "a6" Or Split(YOUR_SENTENCE, " ")(1) = "a6" Then
    MsgBox "found"
End If

Open in new window

it's easy to do.
Dim regex as Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^a6"
MsgBox regex.test(YOUR_STRING)

Open in new window

Top Expert 2014
Commented:
Since you want first or second word to match, tweak wilcoxonn's code to use this pattern:
(?:^|\n)(a6|\w+ a6)\b

Open in new window


I was testing against multiple lines, so the pattern can be simplified if only applied to a single string:
^(a6|\w+ a6)\b

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
@aikimark: Wow.
I missed he said "first or second word" since he later said "at the beginning of the sentence".

Does VBA RegEx support \b?  I know a lot of regex engines don't.  If not, tweak aikimark's code to:
^(a6|\w+\s+a6)\s

Open in new window

In any case, I'd suggest changing the \w+<space>a6 to \w+\s+a6 to catch non-space or multi-space word-breaks.

Edit: or to be even more permissive:
^(a6|\w+\W+a6)\W

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial