Extracting Numbers in middle of paragraph

%3CLINGO-SUB%20id%3D%22lingo-sub-1111692%22%20slang%3D%22en-US%22%3EExtracting%20Numbers%20in%20middle%20of%20paragraph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111692%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20everyone!%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20this%20formula%20to%20extract%20numbers%20in%20the%20middle%20of%20a%20sentence%20or%20paragraph%2C%20however%2C%20it%20is%20only%20extracting%20two%20of%20my%20numbers%20and%20leaving%20the%20third%20one%20all%20zeros.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%3A%3C%2FSTRONG%3E%20%22I%20am%20trying%20to%20extract%20these%20numbers%20in%20this%20sentence%26nbsp%3B02060874%2C02270190%2C02345347%20using%20this%20formula.%22%3C%2FP%3E%3CP%3E%3CSTRONG%3EMy%20Return%3A%3C%2FSTRONG%3E%26nbsp%3B20608740227019000000000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%3DSUMPRODUCT(MID(0%26amp%3BA25%2C%20LARGE(INDEX(ISNUMBER(--MID(A25%2C%20ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A25)))%2C%201))%20*%20ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A25)))%2C%200)%2C%20ROW(INDIRECT(%221%3A%22%26amp%3BLEN(A25))))%2B1%2C%201)%20*%2010%5EROW(INDIRECT(%221%3A%22%26amp%3BLEN(A25)))%2F10)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3ECan%20someone%20help%20me%20adjust%20this%20so%20I%20am%20able%20to%20pull%20the%20third%20number%3F%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1111692%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111703%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Numbers%20in%20middle%20of%20paragraph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111703%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20number%20do%20you%20want%20to%20extract%20specifically%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111733%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Numbers%20in%20middle%20of%20paragraph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111733%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20use%20Power%20Query%20to%20do%20that%3CBR%20%2F%3E1.%20Click%20in%20the%20data%20and%20press%20CTRL%20%2B%20T%20to%20format%20in%20Excel%20Table%3CBR%20%2F%3E2.%20In%20the%20Data%20tab%2C%20click%20on%20From%20Table%2FRange%3CBR%20%2F%3E3.%20In%20the%20Power%20Query%20editor%2C%20select%20Split%20Column%20By%20Non-Digit%20to%20Digit%3CBR%20%2F%3E4.%20Further%20select%20Split%20Column%20By%20Digit%20to%20Non-Digit.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20will%20extract%20all%20the%20numbers%20including%20the%20leading%20zero.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20helps%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111959%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Numbers%20in%20middle%20of%20paragraph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F520357%22%20target%3D%22_blank%22%3E%40bobby24%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20now%20that%20you%20posted%20the%20same%20question%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fconvert-txt-to-numbers-in-excel-then-change-format%2Fm-p%2F1111676%23M49078%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fconvert-txt-to-numbers-in-excel-then-change-format%2Fm-p%2F1111676%23M49078%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111923%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Numbers%20in%20middle%20of%20paragraph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F520357%22%20target%3D%22_blank%22%3E%40bobby24%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20creates%20a%20number%20(in%20words)%20of%2020-sixtrillion.%20That's%20a%2020%20with%2021%20zeros%20(Thank%20you%20Google).%20When%20you%20display%20it%20as%20a%20normal%20number%2C%20Excel%20can%20handle%20the%20first%2014%20digits%20and%20the%20rest%20become%20zeros.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20will%20create%20a%20continuous%20text%20string%20of%20all%20the%20numbers%20in%20your%20text%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%22%2CTRUE%2CIF(ISNUMBER(--MID(A1%2CROW(%24A%241%3AINDEX(%24A%3A%24A%2CLEN(A1)))%2C1))%2CMID(A1%2CROW(%24A%241%3AINDEX(%24A%3A%24A%2CLEN(A1)))%2C1)%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECredit%20for%20this%20formula%20goes%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20I%20picked%20it%20up%20a%20while%20ago%20from%20another%20topic%20and%20used%20it%20in%20my%20own%20learning%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@bobby24 

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.