Extract only numbers from a cell

[forginganewone]

Example cell values : [TABLE="width: 256"]
<tbody>[TR]
[TD="align: left"]Demo-PAT-DIRBS-05513-C[TABLE="width: 256"]
<tbody>[TR]
[TD="align: left"][TABLE="width: 322"]
<tbody>[TR]
[TD="width: 322, align: left"]Demo-PAC-Center-WirelessService-52515
[TABLE="width: 242"]
<tbody>[TR]
[TD="width: 322, align: left"]Demo-PAT-SectorSplit-05096-OneCell-C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I want a formula which will extract only numbers from a cell but it should start counting numbers after 0.
For example : from this Demo-PAT-DIRBS-05513-C , it should extract 5513.

I tried using below formulas :

=RIGHT($C4,LEN($C4)-(SUM((MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9},$C4)),FIND({1,2,3,4,5,6,7,8,9},$C4)))-1))))
^ this failed because in some cells the number value was in mid.

{=SUMPRODUCT(MID(0&C2,LARGE(INDEX(ISNUMBER(--MID(C2,ROW($1:$999),1))* ROW($1:$999),0),ROW($1:$999))+1,1)*10^ROW($1:$999)/10)}
^ this is giving #Num Error

 

[Phuoc]

Try:

=MID($C4,MATCH(TRUE,ISNUMBER(--MID($C4,ROW(INDIRECT("1:"&LEN($C4))),1)),0),5)

C+S+E

 

[sandy666]

also you can try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src[/td][td=bgcolor:#70AD47]Number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Demo-PAT-DIRBS-05513-C[/td][td=bgcolor:#E2EFDA]

5513​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Demo-PAC-Center-WirelessService-52515[/td][td]

52515​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Demo-PAT-SectorSplit-05096-OneCell-C[/td][td=bgcolor:#E2EFDA]

5096​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    C2R = Table.AddColumn(Source, "Number", each Text.Trim([src],{"a".."z","A".."Z","-"})),
    Result = Table.TransformColumnTypes(C2R,{{"Number", Int64.Type}})
in
    Result[/SIZE]

 

[Rick Rothstein]

If all of the numbers in your text are 5 digits long like in your 3 examples, then this normally-entered formula should work for you...

[table="width: 500"]
[tr]
	[td]=0+MID(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")),5)[/td]
[/tr]
[/table]

If, on the other hand, your numbers can be of any length, then use this still normally-entered formula instead...

[table="width: 500"]
[tr]
	[td]=LOOKUP(9E+307,--LEFT(MID(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")),ROW($1:$99)),ROW($1:$99)))[/td]
[/tr]
[/table]

 

[jtakw]

Hi,

I want a formula which will extract only numbers from a cell but it should start counting numbers after 0.

Does that mean, as in your 2nd sample, if the number Does Not start with a 0 (zero), don't extract it?

Book1
	C	D
4	Demo-PAT-DIRBS-05513-C	5513
5	Demo-PAC-Center-WirelessService-52515
6	Demo-PAT-SectorSplit-05096-OneCell-C	5096
Sheet439

Cell Formulas
Range	Formula
D4	=IFERROR(LEFT(SUBSTITUTE(MID(C4,FIND("-0",C4)+2,100),"-",REPT(" ",100)),100)+0,"")

 

[Phuoc]

[TABLE="width: 500"]
<tbody>[TR]
[TD]=LOOKUP(9E+307,--LEFT(MID(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")),ROW($1:$99)),ROW($1:$99)))[/TD]
[/TR]
</tbody>[/TABLE]

a little shorter:

=LOOKUP(9E+307,--MID(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")),ROW($1:$99)))

 

[Michael M]

OR a UDF

Function Num(ByVal txt As String) As String
Dim X As Long 'original by Rick Rothstein    'This sample by Jerry Beaucaire
    For X = 1 To Len(txt)
        If Mid(txt, X, 1) Like "*[!0-9]*" Then Mid(txt, X, 1) = Chr(1) ' Leave only numbers
    Next
Num = Replace(txt, Chr(1), "")
End Function

 

[jtakw]

a little shorter:

=LOOKUP(9E+307,--MID(C4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C4&"0123456789")),ROW($1:$99)))

Even shorter:

=-LOOKUP(1,-MID(C4,MIN(FIND({0,1,2,3,4}+{0;5},C4&1/17)),ROW($1:$99)))

 

[FlameRetired]

Borrowed this one from XOR LX here

Array entered.

=NPV(-0.9,IFERROR(--MID(C4,99-ROW($1:$99)+1,1),""))/10

 

[James006]

Borrowed this one from XOR LX here

Array entered.

=NPV(-0.9,IFERROR(--MID(C4,99-ROW($1:$99)+1,1),""))/10

XOR LX ....

Brilliant ... as usual ...