How do I split a cell into text and number

[Hwalker1]

I have a table with thousands of lines like this:
a few words followed by a number
example: interest repayment 123456
The text can have many variants in content and length and the number is always a whole number - an integer from 5 to 10 digits long.
I need to select the number from Col A and reproduce it in an adjacent cell.

Like:
Col A ::------------------------- :: Col B
interest repayment 123456 123456
All help welcomed

 

[XLPadawan]

in A1: interest repayment 123456
Select column A

Copy
Paste values into column D​

Select column D, then Data Ribbon -> Data Tools section -> Text to Columns
Text to Columns settings:

Select Delimited
Set the delimiter to Space and check the box to treat consecutive delimiters as one
Finish​

Select a cell SOMEWHERE out to the right of all your text (I chose J1), type =IF(ISNUMBER(D1),D1,0)

Extend this formula to the right as far as you need it. I extended mine from J1 to M1.​

in B1: =MAX(J1:M1)

 

[XLPadawan]

I just had a cool epiphany and found a simpler solution:

A1 interest repayment 123456
B1 =VALUE(H1)
D1 =IFERROR(MID(A1,FIND(" ",A1)+1,99),"")
Extend the formula in D1 across to H1.
After extending the formula to the right, H1 should contain:
=IFERROR(MID(G1,FIND(" ",G1)+1,99),G1)​

 

[Luck]

Try this
Function Digits(myString As String) As String
Dim n As Long
For n=1 to Len(myString)
If Mid(myString,n,1) Like "[0-9]" then Digits=Digits & Mid(myString,n,1)
End if
Next n
End Sub
then you can use it like worksheet function
in B2
= Digits(A2)