Extracting numbers from a cell

[modiria50989]

Is there any Excel formula (not VBA), that I can extract numbers from a cell?

e.g.

B:271543GARDEN PL; 272512ESS H71 ;BT1 FLO QUAD 1 43170 345-ROCK CK3 345 (1). I want to see 271543 272512 71 1 1 43170 345 345 1

<tbody>
</tbody>

Thank you

 

[Oaktree]

I think you're missing the 3 in CK3 in your result.

Try

=TRIM(CONCAT(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")+ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }s around your formula in the formula bar)

 

[modiria50989]

I applied the formula on A2 then it became {=TRIM(CONCAT(IF((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=" ")+ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")))}

but the result is #NAME ?

I think you're missing the 3 in CK3 in your result.

Try

=TRIM(CONCAT(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")+ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }s around your formula in the formula bar)

 

[Oaktree]

CONCAT, along with the similar TEXTJOIN, is a (long awaited) new function in Excel 2016.

If you are using an older version of Excel, there is no native way to concatenate ranges like that.

I'm afraid your options would be:
1) Install an add-in like morefunc.dll that includes the MCONCAT function
2) Make your own UDF, with examples such as those in this thread: https://www.mrexcel.com/forum/excel-questions/365691-concatenate-multiple-cells-array-formula.html
3) Use a whole bunch of helper columns (each with something like

=if(OR(MID($A2,COLUMN() - COLUMN($A2)= " ",ISNUMBER(MID($A2,COLUMN() - COLUMN($A2),1)+0)),MID($A2,COLUMN() - COLUMN($A2),1),"")

and concatenate them one at a time with =G2&H2&...&AZ2

 

[Rick Rothstein]

...but the result is #NAME ?

The problem is the CONCAT function was added in a later version of Excel (2016 I think) than the version you are now using. Because of that and the number of digits you want to end up with inside a single cell, you will need to use a VBA solution to accomplish what you want. Here is a UDF (user defined function) that will do it...

Function Digits(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  Digits = Application.Trim(S)
End Function

 

[jtakw]

Hi,

Just FYI, CONCAT, (along with many other functions) is Only available with an Office 365 subscription.

 

[modiria50989]

Rick, this works, thank you very much.