Extracting numbers from a string in each cell

Question

I'm trying to write code that extracts X consecutive numbers from text.

For example, if I want to extract 5 consecutive numbers in my text:

Cell A1: dsuad28d2hr 22222222 11111 d33d11103
Cell B2: 11111 (wanted)

I could make it work for texts with only 5 numbers but the problem is if my text contains other consecutive numbers higher than 5.

Sub ExtractNum2()
    Dim Caract() As String
    Dim i As Integer
    Dim j As Integer
    Dim z As Integer
    Dim cont As Integer
    Dim goal As Integer
    Dim Protocolo() As String
    Dim cel As String
    Dim lin As Long

    lin = Range("A1", Range("A1").End(xlDown)).Rows.Count 'Repeat for each line
    For z = 1 To lin
        cel = Cells(z, 1)
        ReDim Caract(Len(cel))
        ReDim Protocolo(Len(cel))
        cont = 0
        For i = 1 To Len(cel)
            Caract(i) = Left(Mid(cel, i), 1)
            If IsNumeric(Caract(i)) Then 'Character check
                cont = cont + 1
                Protocolo(cont) = Caract(i)
                'If Not IsNumeric(Caract(6)) And cont = 5 Then**
                If cont = 5       '
                    Dim msg As String
                    For j = 1 To 5
                        msg = msg & Protocolo(j)
                    Next j
                    Cells(z, 2) = msg 'fills column B
                    msg = ""
                End If
            Else
                cont = 0
            End If
        Next i
    Next z 'end repeat
End Sub

I'm trying to use:

 If Not IsNumeric(Caract(6)) And cont = 5 Then

But it is not working, my output is: B2: 22222 but I want 11111. What am I missing?

EDIT Sorry i wasnt clear. I want to extract X numbers with 6>x>4 (x=5). I dont want 22222 since it has 8 consecutive numbers and 11111 has 5 in my example.

Welcome to Stack Overflow! I don't understand: 22222 is also 5 consecutive numbers, so why wouldn't that number be returned too? (in addition to 11111)? It may be helpful if you add some more examples representative of variations in your actual data, as well as a but of an explanation of what you're doing and what you need to finish with. Check out the tour (you'll earn your first badge!) and see how to create a minimal reproducible example as well as "How to Ask", plus this checklist from the site's top user. — ashleedawg
– ashleedawg, Commented Sep 3, 2018 at 19:33
This code isn't running at all as-is, you have a line If cont = 5 missing a Then... Anyway, regex is probably the easiest way to do it if that works for you. — ashleedawg
– ashleedawg, Commented Sep 3, 2018 at 19:39
Have a look at the split function, then look through the resultant array and if the length is 5 and the contents are numberic, you'll get what you desire so if array(1)=55555 then len(array(1))=5 and isnumeric will be true — Nathan_Sav
– Nathan_Sav, Commented Sep 3, 2018 at 19:53
@Nathan_Sav has the right idea, that's exactly how I'd do it if you don't want to go with regex. — Fernando J. Rivera
– Fernando J. Rivera, Commented Sep 3, 2018 at 21:13
Sorry i wasnt clear. I want to extract X numbers with 4<x<6 (x=5). I dont want 22222 since it has 8 consecutive numbers and 11111 has 5. — ehdgur
– ehdgur, Commented Sep 4, 2018 at 12:15

JohnyL · Accepted Answer · 2018-09-04 18:55:28Z

4

UDF:

Function GetNum(cell)
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b(\d{5})\b"
        With .Execute(cell)
            If .Count > 0 Then GetNum = .Item(0).SubMatches(0)
        End With
    End With
End Function

UPDATE:

If you want to return error (say, #N/A) instead of callee's default data type, you could write the following:

Function GetNum(cell)
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b(\d{5})\b"
        With .Execute(cell)
            If .Count > 0 Then
                GetNum = .Item(0).SubMatches(0)
            Else
                GetNum = CVErr(xlErrNA)
            End If
        End With
    End With
End Function

edited Sep 4, 2018 at 18:55

answered Sep 3, 2018 at 20:11

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

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

4 Comments

41686d6564 Over a year ago

I think you need to remove the second \b to meet the OP's expected output, or perhaps it should be replaced with \d*. It's not clear based on the OP's examples which one would suit him.

ehdgur Over a year ago

I think this code might suit my need, i will try and return. Thanks in advance guys.

JohnyL Over a year ago

@ehdgur This function will return default value for data type used by calee. Alternatively, it could return error (for instance, #N/A).

ehdgur Over a year ago

Thank you @JohnyL

Yeshwant Mudholkar · Accepted Answer · 2018-09-03 20:12:21Z

I tried this with a Cell containing "Yjuj 525211111x5333332s5" to test whether 2 consecutive 5 characters get catch, and it worked great.

Sub Macro_Find_Five()

    Dim str As String
    Dim tmp As String
    Dim cntr As Integer
    Dim result As String

    str = Sheet1.Cells(1, 1).Value

    tmp = ""
    cntr = 1
    col = 2
    result = ""

    'For Loop for tracing each charater
    For i = 1 To Len(str)

        'Ignore first starting character
        If i > 1 Then

            'If the last character matches current character then
            'enter the if condition
            If tmp = Mid(str, i, 1) Then

                'concatenate current character to a result variable 
                result = result + Mid(str, i, 1)

                'increment the counter
                cntr = cntr + 1

            Else

               'if the previous character does not match
               'reset the cntr to 1
                cntr = 1

               'as well initialize the result string to "" (blank)
                result = ""

            End If

        End If

        'if cntr matches 5 i.e. 5 characters traced enter if condition
        If cntr = 5 Then

           'adding to next column the result found 5 characters same
            Sheet1.Cells(1, col).Value = result

            'increment the col (so next time it saves in next column)
            col = col + 1

            'initializing the variables for new search
            cntr = 1
            tmp = ""
            result = ""

        End If

       'stores the last character
        tmp = Mid(str, i, 1)

        'if first character match concatenate.
        If cntr = 1 Then
            result = result + Mid(str, i, 1)
        End If

    Next i

End Sub

Collectives™ on Stack Overflow

Extracting numbers from a string in each cell

2 Answers 2

UPDATE:

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE:

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related