How to find and copy specific text inside a string VBA?

Question

I have a bunch of strings that i need to extract the phone numbers from, how do I manage to get them from this string and paste in a worksheet knowing that they all have the formatting (??) ????-???? where ? is a random number from 0 to 9 and knowing that there could be multiple phone numbers inside the same string?

Example:

"Acreaves Alimentos. Rodovia Do Pacifico, (68) 3546-4754 Br 317, Km 8, S/N - Zona Rura... Brasileia - AC | CEP: 69932-000. (68) 3546-5544. Enviar "

would return (68) 3546-4754 and (68) 3546-5544

@bingoball read this : http://stackoverflow.com/a/22542835/4678661 — Fabich, Jul 05 '16 at 14:48

score 2 · Accepted Answer · answered Jul 05 '16 at 15:04

I have a snippet of code here which sets up a regular expression for the format you have specified and searches the string, then providing a msgbox for each instance it finds.

You need to ensure that you have added (using Tools->References) the Microsoft VBScript Regular Expressions 5.5 reference, or you will fail to create the RegExp object initially.

The regex pattern in this case is specified to allow a bracket (escaped with a \ since otherwise it has special meaning in a regular expression), then two digits, each of which can be 0-9, a close bracket (escaped again), \s to indicate a space, followed by 4 digits in the character set 0-9, a dash (escaped again) and the final four digits in the 0-9 set.

Don't forget to set the regex Global attribute to True so that it returns all matches.

sString = "Acreaves Alimentos. Rodovia Do Pacifico, (68) 3546-4754 Br 317, Km 8, S/N - Zona Rura... Brasileia - AC | CEP: 69932-000. (68) 3546-5544 . Enviar"

Dim oReg : Set oReg = New RegExp
oReg.Global = True
oReg.Pattern = "\([0-9]{2}\)\s[0-9]{4}\-[0-9]{4}"
Set Matches = oReg.Execute(sString)
For Each oMatch In Matches
    MsgBox oMatch.Value
Next

Should do what you require, based on your details and the string you provided.

How could I change the msgbox line to paste in a excel range/cells? — bingoball, Jul 05 '16 at 15:27
Instead of using a `MsgBox` you would set the value of the range you want, for example: `Thisworkbook.worksheets(1).Range("A1").Value = oMatch.Value`. You'd need to increment the row you were writing to for each match item though... — Dave, Jul 05 '16 at 23:30

score 0 · Answer 2 · answered Jul 05 '16 at 14:47

If the format actually stays the same throughout you can try something like this:

a = "Acreaves Alimentos. Rodovia Do Pacifico, (68) 3546-4754 Br 317, Km 8, S/N - Zona Rura... Brasileia - AC | CEP: 69932-000. (68) 3546-5544. Enviar "
arrNums = Split(a, "(")
For i = 1 To UBound(arrNums)
    num = "(" & Left(arrNums(i), 13)
Next

Kelaref · Answer 3 · 2016-07-05T15:04:04.053

0

This function will return an array containing the numbers:

Function ReturnNumbers(s As String) As variant

    Dim s As String, a As Variant, r As Variant, i As Integer

    a = Split(s, "(")

    ReDim r(1 To UBound(a, 1))

    For i = 1 To UBound(a, 1)
        r(i) = "(" & Left(a(i), 13)
    Next

    ReturnNumbers = r

End Function

edited Jul 05 '16 at 15:04

answered Jul 05 '16 at 14:51

Kelaref

547
1
8
26

How to find and copy specific text inside a string VBA?

3 Answers3