Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:

Leave a Comment

avatar
WymSkPhN
Guest
WymSkPhN

1

Comment awaiting moderation

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc