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:

	A	B
1	String:	my test 1: 123 string 23: 456 that I need to extract numbers 25 789 from.
2	Expected Output:	123456789
3	Logic:	Extract only where there are 3 consecutive numbers (no more, no less)

<tbody>
</tbody>

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!