Return where a number is found in a URL string, only if it is found after a certain number of "/" characters

[chive90]

In C2 I have a URL
In D2 I have a number that is also present in the URL
In J2 I would like to enter a formula that returns a Yes/No if the number in D2 is found in a certain location in C2

A Yes should only be returned if the number is present in the URL string of text between the 8th and 9th "/" character.

If it is found as a single instance before the 8th or after the 9th "/" then a No should be returned. The only caveat to this is if the number is in the URL twice, but one instance is in the correct location (i.e. after the 8th but before the 9th "/") then a Yes should be returned regardless of where the second instance is found.

Thank you

 

[Shloime]

try:

=IF(FIND(D2,SUBSTITUTE(C2,"/","#",8))<FIND("/",SUBSTITUTE(C2,"/","#",8)),"Yes","No")

"#" should be any character that will never be in the URLs

 

[felixstraube]

1. What if you have a 8th "/" then the number but you don't have a 9th "/"? Should that also return yes? Or is this never going to happen?
2. If for example the number is "1234", would it appear in the URL like this?: "firstpartofURL/1234/lastpartofURL" or could it be like this "firstpartofURL/asdf1234asdf/lastpartofURL", that is the number with other characters?

 

[felixstraube]

Considering that 9th "/" hast to be present, and that It can be just the number or the number mixed with other characters between 8th and 9th "/"... how about?:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2			https://www.google.com/maps/something/a/b/c/1234/anothertext/b/c/	1234						yes
3			https://www.google.com/maps/something/a/b/c/asdf1234asdf/anothertext/b/c/	1234						yes
4			https://www.google.com/maps/something/a/b/c/asdf1234asdf	1234						no
5			https://www.google.com/maps/something/a/b/c/1234	1234						no
6			https://www.google.com/maps/something/a/b/c/1234/d/e	1234						yes
7			https://www.google.com/maps/something/a/b/1234/c/d/e	1234						no
8			https://www.google.com/maps/something/a/b/c/d/1234/e	1234						no
Sheet3

Cell Formulas
Range	Formula
J2:J8	J2	=IF(ISNUMBER(SEARCH(D2, TEXTBEFORE(TEXTAFTER(C2, "/", 8), "/"))), "yes", "no")

 

[chive90]

try:

=IF(FIND(D2,SUBSTITUTE(C2,"/","#",8))<FIND("/",SUBSTITUTE(C2,"/","#",8)),"Yes","No")

"#" should be any character that will never be in the URLs

Thanks. This seems to all return "No", despite the number existing. Is this because the number in cell D2 is just the Number, whereas in C2 it is part of a wider string of text between the "/" characters? For example in D2 it might be: 64045465

But in C2 between the 8th and 9th / it might be "text text 64045465"

This should return Yes.

1. What if you have a 8th "/" then the number but you don't have a 9th "/"? Should that also return yes? Or is this never going to happen?
2. If for example the number is "1234", would it appear in the URL like this?: "firstpartofURL/1234/lastpartofURL" or could it be like this "firstpartofURL/asdf1234asdf/lastpartofURL", that is the number with other characters?

Never going to happen. 9th "/" will always be present as each URL ends with a "/".

And yes to your second point which may be why the formula posted by the other user is returning No.

In C2 between the 8th and 9th / it might be "text text 64045465" - in fact it almost always will be with text and rarely the number just by itself - but as this number is still found between the 8th and 9th "/" it should return a Yes.

 

[chive90]

Considering that 9th "/" hast to be present, and that It can be just the number or the number mixed with other characters between 8th and 9th "/"... how about?:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2			https://www.google.com/maps/something/a/b/c/1234/anothertext/b/c/	1234						yes
3			https://www.google.com/maps/something/a/b/c/asdf1234asdf/anothertext/b/c/	1234						yes
4			https://www.google.com/maps/something/a/b/c/asdf1234asdf	1234						no
5			https://www.google.com/maps/something/a/b/c/1234	1234						no
6			https://www.google.com/maps/something/a/b/c/1234/d/e	1234						yes
7			https://www.google.com/maps/something/a/b/1234/c/d/e	1234						no
8			https://www.google.com/maps/something/a/b/c/d/1234/e	1234						no
Sheet3

Cell Formulas
Range	Formula
J2:J8	J2	=IF(ISNUMBER(SEARCH(D2, TEXTBEFORE(TEXTAFTER(C2, "/", 8), "/"))), "yes", "no")

Thank you but I am using Excel 2016 and I do not believe TEXTBEFORE to be a function compatible with this version? Is there an alternative?

 

[felixstraube]

My mistake, here is the formula for 2016:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2			https://www.google.com/maps/something/a/b/c/1234/anothertext/b/c/	1234						yes
3			https://www.google.com/maps/something/a/b/c/asdf1234asdf/anothertext/b/c/	1234						yes
4			https://www.google.com/maps/something/a/b/c/asdf1234asdf	1234						no
5			https://www.google.com/maps/something/a/b/c/1234	1234						no
6			https://www.google.com/maps/something/a/b/c/1234/d/e	1234						yes
7			https://www.google.com/maps/something/a/b/1234/c/d/e	1234						no
8			https://www.google.com/maps/something/a/b/c/d/1234/e	1234						no
Sheet3

Cell Formulas
Range	Formula
J2:J8	J2	=IF(ISNUMBER(SEARCH(D2, MID(C2, SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))+1,SEARCH("\", SUBSTITUTE(C2, "/", "\", 9))-SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))-1))), "yes", "no")

Let me know if it works for you.

 

[chive90]

My mistake, here is the formula for 2016:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2			https://www.google.com/maps/something/a/b/c/1234/anothertext/b/c/	1234						yes
3			https://www.google.com/maps/something/a/b/c/asdf1234asdf/anothertext/b/c/	1234						yes
4			https://www.google.com/maps/something/a/b/c/asdf1234asdf	1234						no
5			https://www.google.com/maps/something/a/b/c/1234	1234						no
6			https://www.google.com/maps/something/a/b/c/1234/d/e	1234						yes
7			https://www.google.com/maps/something/a/b/1234/c/d/e	1234						no
8			https://www.google.com/maps/something/a/b/c/d/1234/e	1234						no
Sheet3

Cell Formulas
Range	Formula
J2:J8	J2	=IF(ISNUMBER(SEARCH(D2, MID(C2, SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))+1,SEARCH("\", SUBSTITUTE(C2, "/", "\", 9))-SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))-1))), "yes", "no")

Let me know if it works for you.

Thank you, this seems to work really well! Appreciate it

I am just being picky now but is there any way to amend the formula so that if the number cell (D2) is blank, it returns a blank in J2?

At present, it returns "Yes" if either the number is found in the correct location, or if the number cell is blank. It returns "No" if the number is found in a different location within the URL which is great.

If it could return a blank if there is no number present in D, that would be brilliant. That way I can differentiate between all 3 outcomes. Thank you

 

[felixstraube]

Thanks for the feedback.
Try this:

=IF(D2="", "", IF(ISNUMBER(SEARCH(D2, MID(C2, SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))+1,SEARCH("\", SUBSTITUTE(C2, "/", "\", 9))-SEARCH("\", SUBSTITUTE(C2, "/", "\", 8))-1))), "yes", "no"))