Extract 8 digit number from a string

[Hans K]

I found a useful formula here

Finding numbers in text string [SOLVED] (daddylonglegs) with the following formula:

=TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),"00000000")

I tried to change this formula to =TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-5)),6)+0),"00000000") to extract a 6 digit number and it seems to work fine, but I have a hard time trying to understand the ROW(INDIRECT("1:"&LEN(A1)-7)),8) part of the formula versus ROW(INDIRECT("1:"&LEN(A1)-5)),6). Someone care to help me understand?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

A small set (5-10 rows) of your data and expected results would also help.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bebo021999]

Assuming A1="ABC DEF GHI 12345678 ZZ", with len = 23, and you want to extract 8 digits.

To iterate from left to right, you can use the following approach:
MID(A1, 1, 8)
MID(A1, 2, 8)
...until ...
MID(A1, 16, 8) (the last value with 16=23-8+1=23-7=len(A1)-7)

To generate the sequence 1...16, you can use the ROW function:
ROW(1:16) = ROW(INDIRECT("1:"&LEN(A1)-7))

Therefore, the MID function becomes:
MID(A1, ROW(INDIRECT("1:"&LEN(A1)-7)), 8)

So, if you want to extract 6 characters, the MID function would be:
MID(A1, ROW(INDIRECT("1:"&LEN(A1)-5)), 6)

Note: This explanation assumes that the starting position for each iteration is 1 character ahead of the previous one.

 

[Peter_SSs]

Thanks for updating your version details.
.. but what about your sample data and expected results?

 

[Hans K]

Assuming A1="ABC DEF GHI 12345678 ZZ", with len = 23, and you want to extract 8 digits.

To iterate from left to right, you can use the following approach:
MID(A1, 1, 8)
MID(A1, 2, 8)
...until ...
MID(A1, 16, 8) (the last value with 16=23-8+1=23-7=len(A1)-7)

To generate the sequence 1...16, you can use the ROW function:
ROW(1:16) = ROW(INDIRECT("1:"&LEN(A1)-7))

Therefore, the MID function becomes:
MID(A1, ROW(INDIRECT("1:"&LEN(A1)-7)), 8)

So, if you want to extract 6 characters, the MID function would be:
MID(A1, ROW(INDIRECT("1:"&LEN(A1)-5)), 6)

Note: This explanation assumes that the starting position for each iteration is 1 character ahead of the previous one.

Thank you very much bebo021999.

 

[Hans K]

Thanks for updating your version details.
.. but what about your sample data and expected results?

I have got a solution and marked it as such.
Sample data: just a string with a 6 digit number or a string with a 8 digit number.
Is that sufficient?

 

[Dave Patton]

LAMBDA.xlsm
	A	B	C
9	ABC DEF GHI 12345678 ZZ		12345678
10	ABC DEF GHI abcdefgi ZZ		Not found
8b

Cell Formulas
Range	Formula
C9:C10	C9	=IFERROR(LET(x,TEXTSPLIT(A9," ",,1),FILTER(x,LEN(x)=8,""))+0,"Not found")

 

[Hans K]

Thank you Dave Patton.
I changed your formula to =IFERROR(LET(x,TEXTSPLIT(A9," ",,1),FILTER(x,LEN(x)=8,"")),"Not found")
to return 04042023 instead of 4042023.

This is a solution but I can't mark it as such as I have already marked another answer as a solution.

 

[Dave Patton]

You didn't provide examples!
Perhaps you need the following for a text result.

LAMBDA.xlsm
	A					F
9	ABC DEF GHI 12345678 ZZ					12345678
10	ABC 04042023					04042023
11	ABC DEF GHI abcdefgi ZZ					not found
8b

Cell Formulas
Range	Formula
F9:F11	F9	=LET(n,LET(x,TEXTSPLIT(A9," ",,1),FILTER(x,LEN(x)=8,"")),IFERROR(IF(n+0>0,TEXT(n,"00000000"),0),"not found"))

 

[Rick Rothstein]

This single formula will return (spill) all of your results (just change the two instances of A9:A11 to the range you want to process)...

=IFERROR(MID(A9:A11,1+LEN(TEXTBEFORE(A9:A11,SEQUENCE(10,,0))),6),"Not Found")