 # 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

# Re: Extracting Numbers in middle of paragraph

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 @Sergei Baklan. I picked it up a while ago from another topic and used it in my own learning process.