How to get 10 numbers from a cell.

[Kamran01]

Dear Experts,

I have a question and i want to get a 10 numbers from different cells.

Column A

1) Hfbfjci03201922101hsg
2) 03209221000hfhbrjfikod
3) Brucinenfnf03128930001
4) Abcd98!3380jhdiokanns

First 3 rows have 10 numbers between text and the last one have two numbers and than 4 numbers together but i want if there are only 10 numbers in a cell, show me those numbers in another cell.

Looking forward for your prompt response,

Best regard,
Kamran Noor

 

[cooper645]

Assuming that your strings are down column A, starting from A1,
Enter the following in cell B1, press Ctrl+Shift+Enter not just Enter

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

drag it down as many rows as you need extracting.

you can then enter in C1

=IF(LEN(B1)=10,B1,"")

and drag that down your rows, hide column B if you wish.


First formula in blue is available here; website link - Not affiliated in any way.

 

[Rick Rothstein]

Dear Experts,

I have a question and i want to get a 10 numbers from different cells.

Column A

1) Hfbfjci03201922101hsg
2) 03209221000hfhbrjfikod
3) Brucinenfnf03128930001
4) Abcd98!3380jhdiokanns

First 3 rows have 10 numbers between text and the last one have two numbers and than 4 numbers together but i want if there are only 10 numbers in a cell, show me those numbers in another cell.

The second one has an 11 digit number in it... what did you want returned from it, the first 10 digits, the last 10 digits or nothing at all?

 

[bradleyluke]

Hello

I have worked on basis of getting 11 numbers

Sub ExtractNumber()
    
    Dim c As Integer
    Dim r As Integer
    Dim leng As Integer
    Dim str As String
    Dim newstr As String
    
    
    
    For c = 1 To 4
        str = Range("A" & c).Value
        leng = Len(str)
        For r = 1 To leng
            If IsNumeric(Mid(str, r, 1)) Then
                newstr = newstr & Mid(str, r, 1)
            End If
        Next r
        If Len(newstr) = 11 Then
            Range("B" & c).Value = newstr
        End If
        newstr = ""
    Next c
End Sub

 

[Rick Rothstein]

I have worked on basis of getting 11 numbers

Why? The OP's thread title and first paragraph clearly state he is after a 10-digit number. My question in Message #3 was directed at the fact that his second example contained an eleven digit number from which two distinct ten digit numbers can be formed and I wanted to know how he wanted that handled.

 

[bradleyluke]

I noticed that my code deleted leading zeros, so you could add this line

Range("B" & c).NumberFormat = "00000000000"

After the line

Range("B" & c).Value = newstr

Hope this is OK

 

[bradleyluke]

I noticed that all three lines had 11 digit numbers, so that is why code was for 11 digits. Perhaps he doesn't want the leading zero but I agree with you that clarification is needed to give exact answer to his question.

 

[Rick Rothstein]

I noticed that all three lines had 11 digit numbers, so that is why code was for 11 digits. Perhaps he doesn't want the leading zero but I agree with you that clarification is needed to give exact answer to his question.

Interesting, I miscounted the other two as having only 10 digits each.

Well then, okay, I now understand why you wrote what you did, but that still begs the question why the OP specifically said he wanted an 10 digit number for the answer. I think my question is still valid... given the cells have 11-digit numbers, what does the OP want.... the first 10 digits, the last 10 digits or nothing (since there is no number with only 10 digits) OR, to add to the question, does he want all 11 digits because he misspoke when he said he wanted 10 digits?

 

[bradleyluke]

I agree, we need OP to come back and let us know exactly which digits he wants, once this is clarified then we can get digits required

 

[Kamran01]

Hello cooper,
Thanks for above formula but still there is a problem.
in this 2nd row, by mistakenly written 11 numbers together, but formula is not showing the first number 0, its showing all 10 words after 0.
i want only 10 numbers which comes together, rest if there are 9 or 11 than don't show me any number.

Thanks,
Kamran