Excel Formula help - select specific segment in url
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I need an excel formula to get a specific segment in a url. prefer non macro here... For example suppose we have this

https://www.acme.com/us/en/solutions/industry/retail/customer-fulfillment.htm

I need to be able to make a cell fetch segment 2 and return "en" or segment 5 "retail"

I am using this formula to get the last non segment in case its helpful

=SUBSTITUTE(REPLACE(A2,1,LOOKUP(2^15,FIND("/",A2,ROW(INDIRECT("1:"&LEN(A2))))),""),".html","")

which results in an answer of "customer-fulfillment"

awarded to kerncy
Tags
excel

Crowdsource coding tasks.

2 Solutions

Winning solution

Hi

It's not a macro, but not a function. But if it is for a "one shot", you can use the tool in excel in "data > Convert" which will be able to split a string into colums.

You will have to use a delimiter ("/" in your case), and then validate. It will split your url into columns having each a different part of your url. You will then just select the column (2,3,...5...12) you need.

You can have a look at this support page for more information

Best regards


Hello Qdev,

I found these formulas that should do what you need: https://builtvisible.com/excel-formulas-for-seos-extracting-url-strings/#folder

The formula you'll want to use is:
=IFERROR(LEFT(TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))+1,255)),FIND("/",TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))+1,255)))-1),TRIM(MID(A2,FIND("#",SUBSTITUTE(A2,"/","#",3))+1,255)))

You'll need to change the number in the SUBSTITUTE functions to get different parts of the URL. Number 3 is "us", number 4 is "en", number 5 is "solutions" and so on. Note that there are 3 substitute functions and you need to change the number in all of them.

Here's a spreadsheet with your example URL.

Let me know if this is what you're looking for, thank you!

View Timeline