Regular Expression Patterns

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
105
Hi.

Let's say I have this code:
VBA Code:
Dim r As RegExp
Dim s As String
s = "abcdefghi jkl mnopq rstuv wxyz"

I want to find the first word following "jkl"

I've tried a few regular expressions, but it always seems to include jkl in the returned substring.

Any suggestions on what pattern to use so, in this case, it would return "mnopq"?

thanks.
Dennis
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Sorry, with regular expressions the definitions must be very clear.

What do you mean, in this case, by a word?

- Is it simply like in the example a contiguous sequence of letters
or
- you have cases like "FinQ1" or "PC$12MKT" or others.

For ex. in the case of "PC$12MKT" do you have
. 1 word: PC$12MKT
. 2 words: "PC" and "MKT" (contiguous sequences of letters),
. or 0 words because both "PC" and "MKT" are not followed of preceded by valid separators?


Please post an exact definition of word:
- what are the characters allowed in the word
and
- what are valid word separators (which are the characters allowed before and after a word, for ex. punctuation?)
 

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
105
In this particular case, a word would be any string of characters (at least one) delimited by white space.

Realistically, my "any string of characters" would just be alphanumeric, but for starters, I'd be OK with any characters.

Does that help? Thanks!
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try this one.
At least it worked when I tested, but I have to confess that Regular Expression manages to surprise me unnecessarily often ...

VBA Code:
Sub testReX()
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
Dim s As String
s = "abcdefghi jkl mnopq rstuv wxyz"
RegEx.Pattern = ".*\bjkl\b\s*(\b\S*\b).*"
Debug.Print RegEx.Replace(s, "$1")
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi Tupe

I tried with

VBA Code:
s = "abcd"

and got the result "abcd"

I think the result should be something like: "jlk" not found ?
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yes, that's how it should be and there are certainly weaknesses in this version as well, but anyway. Here is the new version.

VBA Code:
Sub testReX()
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
Dim s As String, Pat As String, WordToSearch As String

's = "abcd"
s = "abcdefghi jkl mnopq rstuv wxyz"

WordToSearch = "jkl"

Pat = ".*\b" & WordToSearch & "\b\s*(\b\S*\b).*"
RegEx.Pattern = Pat

    If RegEx.Test(s) Then
        Debug.Print RegEx.Replace(s, "$1")
    Else
        Debug.Print """" & WordToSearch & """" & " Not found"
    End If

End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows
If your white space is simply space characters, you could use the following non-RegExp code...
Rich (BB code):
  Dim S As String, AfterMe As String, FirstWord As String
  S = "abcdefghi jkl mnopq rstuv wxyz"
  AfterMe = "jkl"
  FirstWord = Split(Application.Trim(Split(S & AfterMe, AfterMe, , vbTextCompare)(1)) & " ")(0)
Note: If your white space could other than a space character, and if there is not a long list of possibilities, you could use VBA's Replace function to replace them with spaces in the red highlighted S variable.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,670
Members
417,104
Latest member
Nelsini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top