CODEWITHSUNDEEP
vba
bingoball
bingoball

Reputation: 13

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

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

Upvotes: 0

Views: 274

Answers (3)

Dave
Dave

Reputation: 4356

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.

Upvotes: 2

Kelaref
Kelaref

Reputation: 517

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

Upvotes: 0

Abe Gold
Abe Gold

Reputation: 2347

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

Upvotes: 0

Related Questions