How do I split a cell into text and number


Joined
Mar 20, 2014
Messages
2
Reaction score
0
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
 
Joined
Jun 9, 2016
Messages
32
Reaction score
5
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)
 
Ad

Advertisements

Joined
Jun 9, 2016
Messages
32
Reaction score
5
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)​
 
Ad

Advertisements

Joined
Dec 28, 2017
Messages
8
Reaction score
1
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top