VBA find a string within a string based on character format

Question

Have a problem which i cannot seem to beat. Have tried various inStr methods etc but no luck.

So I have a string which contains something like "texttext [email protected] 12/12/2015 1234567891 PST"

The problem is that I need to extract the "1234567891" based on the fact that it is numbers and there is 10 of them, no permanent positions within the text, everything can be in a different place each time around.

Is there a way to do this? My searches led me to splitting the string with " " but I can't figure out how to compare the number to "##########" format to get the result.

Use a VBA UDF and [regex] with a pattern something like [0-9](10). — user4039065
– user4039065, Commented Mar 28, 2016 at 15:00

Gary's Student · Accepted Answer · 2016-03-28 15:02:33Z

4

Consider:

Sub whatever()
    s = "texttext [email protected] 12/12/2015 1234567891 PST"
    ary = Split(s, " ")
    For Each a In ary
        If IsNumeric(a) And Len(a) = 10 Then MsgBox a
    Next a
End Sub

answered Mar 28, 2016 at 15:02

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2446928 Over a year ago

thank you, this should do the trick! Didn't think of the Len, nice one.

user4039065 · Accepted Answer · 2016-03-28 15:39:14Z

2

As an addendum to my comment,

Function tenDigits(str As String)
    Static rgx As Object

    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If

    With rgx
        .Global = False
        .Pattern = "[0-9]{10}"
        If .Test(str) Then tenDigits = .Execute(str)(0)
    End With

End Function

Note that the length is enclosed by braces, not brackets as I originally stated.

answered Mar 28, 2016 at 15:39

user4039065

1 Comment

Ron Rosenfeld Over a year ago

Given what has been posted, I would add word boundary tokens \b around the regex.

Collectives™ on Stack Overflow

VBA find a string within a string based on character format

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related