Extract 5 Digit Number From a String with Excel-VBA

Question

I'm facing a problem as a non dev. I have a column in Excel that contains info as such:

46843 xxxx xxx x 

xxxx 65483 xxxx

xxxx xxx 65432 xxxxx 4 xx

"x" being normal caracters.

What I want is to be able to extract only the numbers of five digits only. I started something like this but struggle to put a loop so that it scans all the string:

Function test()
val_in = "rue 4 qsdqsd CURIE 38320 EYBENS"
Filte = Left(val_in, 5)
If IsNumeric(Filte) Then
    test = Left(val_in, 5)
Else
    sp1 = InStr(1, val_in, " ")
    sp2 = InStr(sp1 + 1, val_in, " ")
    spt = sp2 + sp1

    If spt > 5 Then
        extr = Mid(val_in, spt, 5)
    End If
End If
End Function

How could I turn the part after "Else" into a loop so that it would scan every space of the string and extract only the numbers that contains 5 digits?

QHarr · Accepted Answer · 2018-07-31T20:40:47.550

Using regex

Option Explicit
Public Function GetNumbers(ByVal rng As Range) As Variant
    Dim arr() As String, i As Long, matches As Object, re As Object
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\b\d{5}\b"

        If .test(rng.Value) Then
            Set matches = .Execute(rng.Value)

            ReDim arr(0 To matches.Count - 1)
            For i = LBound(arr) To UBound(arr)
                arr(i) = matches(i)
            Next i

        Else
            arr(i) = rng.Value
        End If
    End With
     GetNumbers = Join(arr, ",")
End Function

Data:

If there is more than one match a comma separated list is returned.

Sorry for the delay, I tried it and it worked perfectly, I could even play arround with it if I was looking at shorter than 5 digit. Life saving, thanks a lot! — Nad, Aug 09 '18 at 07:31

Vityata · Answer 2 · 2018-07-30T09:30:07.800

1

Sub TestMe()

    Dim valIn As String
    valIn = "rue 4 qsdqsd CURIE 38320 EYBENS 43443"

    Dim i As Long
    Dim splitted As Variant

    splitted = Split(valIn)

    For i = LBound(splitted) To UBound(splitted)
        If IsNumeric(splitted(i)) And Len(splitted(i)) = 5 Then
            Debug.Print splitted(i)
        End If
    Next i

End Sub

Considering that in your example you mean that the 5 digit numbers are splitted by space, the above works. It splits the string by space to an array and loops through the elements of the array. If the element is with 5 chars and is numeric, it prints it.

If the rule for the spaces is not something that one can count on, here is a different implementation:

Sub TestMe()

    Dim valIn As String
    valIn = "44244rue4qsdqsdCURIE383201EYBENS43443"

    Dim i As Long
    For i = 1 To Len(valIn) - 4
        If IsNumeric(Mid(valIn, i, 5)) Then
            Debug.Print Mid(valIn, i, 5)
        End If
    Next i

End Sub

It starts looping through the string, checking whether each 5 chars are numeric. When you have numeric 6 chars, it gives two results - 1 to 5 and 2 to 6. Thus 383201 is "translated" as the following 2:

38320
83201

edited Jul 30 '18 at 09:30

answered Jul 30 '18 at 09:24

Vityata

42,633
8
55
100

Thx for your answer. If I want to use the macro on a whole column and print it on the next one, how should I do that? I found that it should work by doing ValIn = Range(C2:C2000).Select but struggle with the result printing – Nad Jul 30 '18 at 12:41
@Nad - you are welcome. Concerning the column usage, a for-each loop, looping through each cell of the column would be a solution. Then the `valIn` would be the `cell` and with `cell.Offset(0,1)` you can refer the next column. – Vityata Jul 30 '18 at 12:44
I'm afraid you lost me at `Cell.Offset(0.1)`, could you please be a little more specific :) – Nad Jul 30 '18 at 12:52
@Nad - take a look at the answers here - https://stackoverflow.com/questions/47515141/cell-address-in-a-loop However, your offset is `.Offset(0,1)` and not the one referred there. – Vityata Jul 30 '18 at 12:55
I can somehow get it there, still not easy to assimilate, thanks for the help. – Nad Aug 09 '18 at 07:32

score 0 · Answer 3 · answered Jul 30 '18 at 09:30

If you have always space between words/numbers then this should do

Sub test()

    Dim TestStr As String
    Dim Temp As Variant
    Dim i As Long, FoundVal As Long

    TestStr = "rue 4 qsdqsd CURIE 38320 EYBENS"

    Temp = Split(TestStr, " ")

    For i = 0 To UBound(Temp)
        If Len(Trim(Temp(i))) = 5 And IsNumeric(Temp(i)) Then
            FoundVal = Temp(i)
            MsgBox FoundVal
        End If
    Next i

End Sub

score 0 · Answer 4 · answered Jul 23 '20 at 11:55

From the solution you are trying to apply (creating custom function in VBA) I understand that you actually need to use it in a formula.

To find number with five digits from cell A1 you can use the following formula without VBA:

=IF(ISERROR(FIND("0"&REPT("1",5)&"0",CONCAT(0+(ISNUMBER(-MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+2)),1))+0)))),"",MID(A1,FIND("0"&REPT("1",5)&"0",CONCAT(0+(ISNUMBER(-MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+2)),1))+0))),5))

To search for other number of digits change the three occurrences of number 5 to your desired digits count in the formula.

Extract 5 Digit Number From a String with Excel-VBA

4 Answers4