Formula to remove text but leave numbers

[thedeadzeds]

Guys, is there a way to use a formula to remove all text but only leave the numbers (as per example below). Also, remove the zero from the beginning although excel normally does this anyway.

Thanks


[TABLE="class: grid, width: 283"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tel number[/TD]
[TD]Formula to remove text[/TD]
[/TR]
[TR]
[TD]Mobile: 01234567899[/TD]
[TD="align: right"]1234567899[/TD]
[/TR]
[TR]
[TD]Work: 01234567899[/TD]
[TD="align: right"]1234567899[/TD]
[/TR]
[TR]
[TD]01234567899 mr[/TD]
[TD="align: right"]1234567899[/TD]
[/TR]
</tbody>[/TABLE]

 

[ravisingh]

I found an elegant formula at
https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html

for data in cell A5 it gives the formula:
=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)

 

[thedeadzeds]

thank you

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Tel number[/td][td][/td][td=bgcolor:#70AD47]Numbers[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mobile: 01234567899[/td][td][/td][td=bgcolor:#E2EFDA]

1234567899​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Work: 01234567899[/td][td][/td][td]

1234567899​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]01234567899 mr[/td][td][/td][td=bgcolor:#E2EFDA]

1234567899​

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

[SIZE=1]let
    C2R = List.Transform({32..45,47,58..126}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Trim = Table.AddColumn(Source, "Numbers", each Text.Trim([Tel number],C2R)),
    TypeNumber = Table.TransformColumnTypes(Trim,{{"Numbers", type number}}),
    ROC = Table.SelectColumns(TypeNumber,{"Numbers"})
in
    ROC[/SIZE]