Go Back

Strip protocol and trailing slash from URL

If you have a URL and want to find a double slash (“//”) and remove the protocol (https://) from it, you will use a formula that combines the MID, FIND and LEN functions. Throughout the article, we will provide you with a complete guide to using this formula.

Strip protocol and trailing slash from URL

Using the “Strip Protocol and Slash in URL” technique, we can remove selected part of any URL. That is, we remove the protocol for any given link (“HTTP://”).

Formula

=MID(url,FIND("//",url)+2,LEN(url)-FIND("//",url)-1-(RIGHT(url)="/"))

Explanation                      

Strip protocol and trailing slash from URL uses the following arguments:

URL: URL is the link of the website.

FIND: FIND locates a double slash (“//”) in the URL and displays its position as a number.

LEN: The LEN function calculates the length of the original URL, subtracting the position of “//” minus 1 from it.

RIGHT: The RIGHT function is used to extract the last character compared to a single slash (“/”).

Example 1

In the given example, the formula in C4 is:

=MID(B4,FIND("//",B4)+2,LEN(B4)-FIND("//",B4)-1-(RIGHT(B4)="/"))

The heart of this formula is the MID function, which takes out the text in the link, starting with the character after a double slash (“//”) and ending at the character before the trailing slash (“/”)

=MID(URL, start, chars)

In this example, the URL is coming from B4.

The start of the URL is calculated by the FIND function:

=FIND("//",B4)+2

Here FIND function is returning the position of “//” as a number in the link, to start extracting from the next character 2 is added in the FIND function.

=LEN(B5)-FIND("//",B5)-1-(RIGHT(B5)="/")

Figure1. Example of Strip protocol and trailing slash from URL

Here the function RIGHT extracts the last character compared to a single slash (“/”).

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, can you add trailing slash at the end of all URLs
Solved by B. A. in 29 mins
Need to add trailing slash / to list of urls which don't have them
Solved by C. E. in 30 mins
I need formula to strip cell from particular phrases. Example cell: Lokalizacja: Kraj: POLSKA Miasto: WARSZAWA Adres: JMP S.A. BIEDRONKA 1906 Strip: "Lokalizacja: Kraj: ", "Miasto: ", "Adres: " I have similar cells in few columns, i need to strip from all of them and then combine into one cell
Solved by B. B. in 16 mins

Leave a Comment

avatar