Forum Discussion
Extracting Numbers in middle of paragraph
Hey everyone!
I am using this formula to extract numbers in the middle of a sentence or paragraph, however, it is only extracting two of my numbers and leaving the third one all zeros.
Example: "I am trying to extract these numbers in this sentence 02060874,02270190,02345347 using this formula."
My Return: 20608740227019000000000
=SUMPRODUCT(MID(0&A25, LARGE(INDEX(ISNUMBER(--MID(A25, ROW(INDIRECT("1:"&LEN(A25))), 1)) * ROW(INDIRECT("1:"&LEN(A25))), 0), ROW(INDIRECT("1:"&LEN(A25))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A25)))/10)
Can someone help me adjust this so I am able to pull the third number?
2 Replies
See now that you posted the same question here:
Your formula creates a number (in words) of 20-sixtrillion. That's a 20 with 21 zeros (Thank you Google). When you display it as a normal number, Excel can handle the first 14 digits and the rest become zeros.
This formula will create a continuous text string of all the numbers in your text:
=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1)),MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1),""))Credit for this formula goes to SergeiBaklan. I picked it up a while ago from another topic and used it in my own learning process.
