RegExp

[tyija1995]

Hi all,

I've tried reading up on regular expressions but I am drawing a blank as to how to extract from a string 3 consecutive digits (no more, no less).

So if I have:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]String:[/TD]
[TD]my test 1: 123 string 23: 456 that I need to extract numbers 25 789 from.[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Expected Output:[/TD]
[TD]123456789[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Logic:[/TD]
[TD]Extract only where there are 3 consecutive numbers (no more, no less)[/TD]
[/TR]
</tbody>[/TABLE]

I have wrote this but it's doing the opposite and I can't figure out how to negate it...

Function Extract3Digits(txt As String) As String
    Dim rgx As Object
    Set rgx = CreateObject("VBScript.RegExp")
        With rgx
            .Global = True
            .Pattern = "\d{3}"
            Extract3Digits = .Replace(txt, "")
        End With
End Function

Used on my string provided I get:
"my test 1: string 23: that I need to extract numbers 25 from."
So the bit I have replaced essentially is the bit I want to reference, but I don't know how to.

Thanks!

 

[tyija1995]

Re: RegExp Help

Apologies - found an answer now !

 

[Rick Rothstein]

Re: RegExp Help

You can also do this without using Regular Expressions...

Function Extract3Digits(ByVal S As String) As String
  Dim X As Long, Arr As Variant
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  Arr = Split(Application.Trim(S))
  For X = 0 To UBound(Arr)
    If Len(Arr(X)) <> 3 Then Arr(X) = ""
  Next
  Extract3Digits = Replace(Join(Arr), " ", "")
End Function

 

[tyija1995]

Re: RegExp Help

Hi Rick,

Thanks for this other solution - very good! Just saw your reply on the other post with this too, have made a note of this and will use it going forward should I need it, many thanks!