Need help extracting text from a URL

Question

I have a spreadsheet with hundreds of URLs. Each URL has a search-engine friendly name in it, followed by a numeric ID, with a "/1" at the end. here's an example:

http://www.somesite.com/directory/varying-file-names-Guide/431/1

in this case, the 431 is the numeric ID. I'm trying to extract this numeric ID into its own column, but my lack of Excel knowledge is definitely holding me back. I've found a few examples, but my attempts to customize these example to match my needs results in errors. In all cases, the value I need to extract will always be between "-Guides/" and "/1" in the URL.

any help would be greatly appreciated.

thanks!

Answer 1

Thanks to everyone who answered. A co-worker of mine also came up with a different approach that also worked.

=MID(A6,FIND("uide/",A6,1)+5,LEN(A6)-FIND("tion/",A6,1)-6)

I thought I'd include this for anyone else facing the same issue. Interesting to see the various ways people approach the problem.

Answer 2

Assuming your URL in A1 try this formula in B1 to extract that number

=REPLACE(LEFT(A1,LEN(A1)-2),1,SEARCH("Guide/",A1)+5,"")

assumes "Guide" not "Guides" - if it's "Guides" then the 5 needs to be a 6.....,i.e.

=REPLACE(LEFT(A1,LEN(A1)-2),1,SEARCH("Guides/",A1)+6,"")

...or a different approach that will extract any number that ends at the 3rd charcater from the end

=LOOKUP(10^5,RIGHT(LEFT(RIGHT(A1,7),5),{1,2,3,4,5})+0)

extracts up to 5 digits

Answer 3

This formula should work regardless of the word preceding the numeric ID. Assuming your URL is in cell A1,

In cell B1:

=SUBSTITUTE(LEFT(A1, LEN(A1) - 2), "/","||", LEN(LEFT(A1, LEN(A1) - 2)) - LEN(SUBSTITUTE(LEFT(A1, LEN(A1) - 2), "/","")))

In cell C1:

=RIGHT(B1,LEN(B1)-FIND("||",B1)-1)