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

 

[ExcelToDAX]

Can you give us an example of the URL?

 

[chive90]

Can you give us an example of the URL?

The URL could vary greatly and the 8 digit number will not always be in the same place - so not sure if an example will help.

But it could be something like:

https://testsite/testname/team/department/year/example test 56807401/example2432/folder/asjdhjashd.pdf

In the above example, Column D should contain: 56807401

 

[Micron]

Not sure if there is anything else you didn't cover but what should happen if you have (just using part of that url)
test 56807401/example24321122/folder/asjdhjashd.pdf
Then you'd have 2 blocks of eight.

 

[Fluff]

How about

Fluff.xlsm
	C	D
1
2	https://testsite/testname/team/department/year/example test 56807401/example2432/folder/asjdhjashd.pdf	56807401
Sheet6

Cell Formulas
Range	Formula
D2	D2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(C2," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()]")

 

[chive90]

Not sure if there is anything else you didn't cover but what should happen if you have (just using part of that url)
test 56807401/example24321122/folder/asjdhjashd.pdf
Then you'd have 2 blocks of eight.

That's a good point. In that instance it would be useful if both numbers could be returned - I'm not sure if this is possible or what the default behavior of the formula below are for example?

How about

Fluff.xlsm
	C	D
1
2	https://testsite/testname/team/department/year/example test 56807401/example2432/folder/asjdhjashd.pdf	56807401
Sheet6

Cell Formulas
Range	Formula
D2	D2	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(C2," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()]")

Thank you this seems to work well in the most part however I have a couple of examples where it is failing.

In the string there is a number: 00044350
The value being returned in D for this entry is 44350.

There is another example in the string the value is: 01012019
The value being returned is: 1012019

Is there any way to resolve it dropping off what looks like any initial 0's?

Also see my post above re the behavior if two or more sets of 8 are found - what is the default behavior here and is it possible for both to be returned?

Thank you!

 

[Fluff]

How about

Fluff.xlsm
	C	D	E
1
2	https://testsite/testname/team/department/year/example test 56807401/example2432/folder/asjdhjashd.pdf	56807401
3	https://testsite/testname/team/department/year/example test 00044350/example 11112432/folder/asjdhjashd.pdf	00044350	11112432
Sheet6

Cell Formulas
Range	Formula
D2:E3	D2	=IFERROR(TEXT(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($C2," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()]["&COLUMNS($D2:D2)&"]"),"00000000"),"")

 

[chive90]

How about

Fluff.xlsm
	C	D	E
1
2	https://testsite/testname/team/department/year/example test 56807401/example2432/folder/asjdhjashd.pdf	56807401
3	https://testsite/testname/team/department/year/example test 00044350/example 11112432/folder/asjdhjashd.pdf	00044350	11112432
Sheet6

Cell Formulas
Range	Formula
D2:E3	D2	=IFERROR(TEXT(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($C2," ","/"),"/","</m><m>")&"</m></k>","//m[string-length()=8 and number()]["&COLUMNS($D2:D2)&"]"),"00000000"),"")

Thanks! This solves the issue with any numbers starting with 0's.

I was unable to get a second block of 8 numbers in the next column across though.

I am now entering the Formula in E, the text string is in C. I tested with 2 batches of 8 numbers in C8 and entered the following in E8:

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

However, nothing appeared in F8, despite two blocks of 8 being in the C8 string like this: https://testsite/testname/team/department/year/00044350/example/folder/65089045/

 

[Fluff]

It works for me, try confirming the formula with Ctrl Shift Enter, rather than just Enter.

 

[chive90]

It works for me, try confirming the formula with Ctrl Shift Enter, rather than just Enter.

Sadly still nothing even with the curly brackets from Ctrl Shift Enter.

Which part of the formula states to output a second value in the next column? Have I changed all of the necessary parts to enter in E and for any second batches of 8 to be inputted into F?

Thanks!