Extract 8 character number from text string

[chive90]

In Column C I have a URL which will sometimes contain an 8 character number.

In Column D I wish to enter a formula which will extract this 8 character number. If an 8 character number is not found, it will leave the cell blank.

I have searched and come across multiple formula's that do not work properly for my requirement. For example, I found: =MAX(IF(ISNUMBER(--MID(C2,ROW(INDIRECT("1:"&LEN(C2))),8)),--MID(C2,ROW(INDIRECT("1:"&LEN(C2))),8)))

However this also seems to return a random 5 digit number when a date is captured in Column C.

I only wish for 8 digit numbers to be returned when they are together as one string. For example 87438483 would be returned, but 877 32 77 9 would not.

There could be instances of less or more numbers together, for example 10 characters 8467309238. I do not want the first 8 returned in this case, this example should return a blank.
I also do not want 8 characters returned if they contain a mixture of other characters and numbers e.g. 10/10/2024 - this is 8 numbers but not 8 continual numbers so a blank should be returned.

Thanks for your help

 

[Fluff]

This part determines whether it returns, the 1st, 2nd etc value ["&COLUMNS($E8:E8)&"]
What happens if you use

=IFERROR(TEXT(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($C8," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()][2]"),"00000000"),"")

assuming C8 has 2 8 digit numbers

 

[chive90]

=IFERROR(TEXT(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($C8," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()][2]"),"00000000"),"")

This now returns the second of the two 8 digit numbers in C8, in E8. But none in F8.

The formula before returned the first of the two 8 digit numbers, and this updated formula returns the second of the two 8 digit numbers.

 

[Fluff]

Must have something to do with the way that 2016 handles the result from the filterxml function.
All i can suggest is you use that formula in F & for E replace the [2] with [1]

 

[Micron]

Perhaps a vba function would solve compatibility issues. Not sure what that would look like!

 

[chive90]

Must have something to do with the way that 2016 handles the result from the filterxml function.
All i can suggest is you use that formula in F & for E replace the [2] with [1]

That's fine. Using the formula in F with a [2] and G with a [3] etc seems to work perfectly so happy to do that.

Many thanks for all of your help as always.

 

[Fluff]

You're welcome & thanks for the feedback.