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 (“/”).