Extract a 5 Digit Number From Inconsistent Text Strings

[jeffgibson55]

I know how to extract a number from a text string using:

LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0123456789")),ROW($1:$10000))))

But in my current data set I have thousands of inconsistent text strings some of which contain other numbers that I don't care about - with the above formula it just grabs the first number it finds left to right. The number I need to grab is always a 5 digit number. Is there any formula I can use that would only grab out a 5 digit number ignoring all other numbers? Here's an example text string: "9/14/2010.NTL.TeleBroc.55129T_V1_N" where 55129 is the desired extract value but like I said the other text strings don't necessarily follow this example's format/delimiters.

Thanks!
Jeff

 

[pgc01]

I suppose, any formula can fail if one would try enough

Well, I'm sure you have seen this here a lot, everyday.
We always try to point out to each other where the formulas fail.
This way we can improve the solution, either by improving the formula or by trying some other approach.

It is the "e" that causes the problem.

In this we disagree. The "e" problem would only be in rows 4 and 5, and all 6 rows give wrong answers.
The formula should find no match in any of the strings, since none of the 6 strings in this example has 5 (and 5 only) consecutive digits.

In this case maybe you make some changes in the formula so that some (or all) of the problems you found are dealt with.

 

[AlKey]

Thank you pgc01
Did the last formula failed too? Maybe I misunderstood what the out come should be on the example you provided

 

[pgc01]

Hi

If I understood correctly, we are trying to extract a 5 (and only 5) consecutive digits string.

None of the 6 strings in this last example has 5 (and 5 only) consecutive digits and so the formula should error out or give some message like "Not found" for all the strings.

 

[AlKey]

I guess, I totally misunderstood the last exercise.

 

[Daljeet Kaur]

Thanks a lot I stuck with this from last week now its solved

 

[Codeplayer]

Reviving an old thread, since the formula

I'm glad it helped Jeff.

Also a formula solution, please test. In B1:

=LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5))

did not work for me.

But it is an excellent idea how to do this. I fixed it up a little and here is what works for me:
(EU format)

=IF(ISERROR(FIND("0"&REPT("1";5)&"0";CONCAT(0+(ISNUMBER(-MID(" "&A1&" ";ROW(INDIRECT("1:"&LEN(A1)+2));1))+0))));"";MID(A1;FIND("0"&REPT("1";5)&"0";CONCAT(0+(ISNUMBER(-MID(" "&A1&" ";ROW(INDIRECT("1:"&LEN(A1)+2));1))+0)));5))

(US format)

=IF(ISERROR(FIND("0"&REPT("1",5)&"0",CONCAT(0+(ISNUMBER(-MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+2)),1))+0)))),"",MID(A1,FIND("0"&REPT("1",5)&"0",CONCAT(0+(ISNUMBER(-MID(" "&A1&" ",ROW(INDIRECT("1:"&LEN(A1)+2)),1))+0))),5))

You just need to change all occurrencies of number 5 to whatever number consecutive digits are you searching for. It is solely based on consecutive numeric digits. If it does not find such number in your string then it leaves the cell blank.

This formula is searching from cell A1

 

[Peter_SSs]

did not work for me.

Welcome to the MrExcel board!

I'm not sure why it didn't work for you - see B1 below - but I suspect an incorrect conversion of commas to semicolons or similar.

Your suggestion uses CONCAT which of course didn't exist when that previous suggestion was made.
Since you have used that and you have Excel 365, what about also using SEQUENCE (& other changes of logic) to significantly reduce the formula length as well as removing the volatile INDIRECT function?

20 07 23.xlsm
	A	B	C
1	9/14/2010.NTL.TeleBroc.55129T_V1_N	55129	55129
2	ABCDEF	#N/A
Sheet2 (2)

Cell Formulas
Range	Formula
B1:B2	B1	=LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1)))+{0,1,2,3,4,5,6},1))+0={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5))
C1:C2	C1	=MID(A1,FIND("0111110",0&CONCAT(--(ISNUMBER(-MID(A1,SEQUENCE(LEN(A1)),1))))&"0111110"),5)