Regular Expression to return only part of the search. e.g. find "15th" but return just "th" (1 Viewer)

[bignose2]

Hi
Not too good with regular expressions, I have below which returns "15th"
Ideally I would like it to just return the th, nd, st part

ResultX = RegExSearch("15th December", "\b(\d+)(st|nd|rd|th)\b")


I could simply use Right(ResultX,2) but did just think this is probably done in the pattern, 1 less command

Thanks I/A

------------------------------------------------------
I copied from elsewhere but seems standard'ish
------------------------------------------------------
Public Function RegExSearch(ByVal text As String, ByVal extract_what As String, Optional seperator As String = "") As String
' https://stackoverflow.com/questions/8146485/returning-a-regex-match-in-vba-excel ' I renamed
Dim i As Long, j As Long
Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = extract_what
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.Count - 1
For j = 0 To allMatches.Item(i).submatches.Count - 1
result = result & seperator & allMatches.Item(i).submatches.Item(j)
Next
Next

If Len(result) <> 0 Then
result = Right(result, Len(result) - Len(seperator))
End If

RegExSearch = result


End Function

 

[June7]

I am not finding structure for RegEx to directly do it so Right(result, 2) seems adequate.

Or use:

result = result & separator & allMatches.Item(i).SubMatches.Item(j)
If IsNumeric(result) Then result = ""

And the Len() code is not needed.

BTW, correct spelling "separator"

Also note for future - should post lengthy code between CODE tags to retain indentation and readability.

 

[arnelgp]

Public Function RegExSearch(ByVal text As String, Optional ByVal extract_what As String = "([0-9]{1,2})(st|nd|rd|th)+", Optional separator As String = ",") As String
    ' https://stackoverflow.com/questions/8146485/returning-a-regex-match-in-vba-excel ' I renamed
    ' modified by arnelgp
    '
    ' to extract the Ordinal number,
    ' use "([0-9]{1,2})(st|nd|rd|th)+" as Pattern (extract_what)
    '
    Dim i As Long, j As Long
    Dim result As String
    Dim allMatches As Object
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    
    RE.pattern = extract_what
    RE.Global = True
    RE.IgnoreCase = True
    Set allMatches = RE.Execute(text)
    
    For i = 0 To allMatches.Count - 1
        'Debug.Print allMatches(i).Value
        result = result & RE.Replace(allMatches(i), "$1") & separator
        'For j = 0 To allMatches.Item(i).submatches.Count - 1
        '    result = result & seperator & allMatches.Item(i).submatches.Item(j)
        'Next
    Next
    
    If Len(result) <> 0 Then
        result = Left$(result, Len(result) - Len(separator))
    End If
    
    RegExSearch = result


End Function

 

[June7]

I tested and this pulls 15 not "th".

 

[sxschech]

Changing $1 to $2 worked for me

 result = result & RE.Replace(allMatches(i), "$2") & separator

? RegExSearch("this is using $2 to get the 15th December to show what is wanted")
th