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

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)

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)