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

 

[Kamran01]

Hello Rick,

Yes it was typed by mistakenly, i apologize.
First and third rows have 10 numbers together, if a cell have <>10 numbers than don't show me and if equal to 10 than show me those numbers including first 0 if there is.

Thanks,
Kamran

 

[Kamran01]

Hello Bradley,

Thanks for vba query, i got the same answer. by mistakenly it was written 11 numbers in second row together including 0.
but the answer is showing excluding 0 even the first number is 0.

Thanks
Kamran

 

[Kamran01]

Hello bradley,

Thank you, there is a conflict in 2nd row. in this case there are 11 numbers together and i want if there are only 10 numbers are together than show me.
if there are number <>10 than don't show me any number.

Thanks
Kamran

 

[Kamran01]

Hello Rick,

I am apologizing, it was a typo but the case is similar with my question.
i have a data of my employees in one cell individually, there isn't any space or other option to convert text to column. exactly 10 digits belong to cell phone number and i want to separate a number from that specific cell.
Hope you understand,

Thanks
Kamran

 

[Kamran01]

Hello cooper,
Will you please describe this formula, i am fully confused how you used this.

Thanks,
Kamran

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.

 

[cooper645]

Kamran, i have amended to formula so you don't have to use a helper column.

=IF(LEN(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))=10,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),"")

Select Cell B1, paste it into the formula bar, then press Ctrl+Shift+Enter
Then drag it down column B as far as your data goes down column A.

If you format the column as Custom; 0000000000

and ensure that you only have 10 digits (NONE of your examples contain ONLY 10 digits)

 

[Rick Rothstein]

Hello Rick,

Yes it was typed by mistakenly, i apologize.
First and third rows have 10 numbers together, if a cell have <>10 numbers than don't show me and if equal to 10 than show me those numbers including first 0 if there is.

Here is a UDF (user defined function) that you can use...

[table="width: 500"]
[tr]
	[td]Function TenDigits(S As String) As String
  Dim X As Long
  For X = 1 To Len(S) + 2
    If Mid("x" & S & "x", X, 12) Like "*[!0-9]##########[!0-9]*" Then
      TenDigits = Mid(S, X, 10)
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TenDigits just like it was a built-in Excel function. For example,

=TenDigits(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Kamran01]

Thank you cooper for your useful formula.

 

[Kamran01]

Thanks Rick, Issue has been resolved with your vba query.