How to strip protocol and trailing slash from URL in Excel
To remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formula based on the MID, FIND, and LEN functions.
Formula
=MID(url,FIND("//",url)+2,LEN(url)-FIND("//",url)-1-(RIGHT(url)="/"))
Explanation
In the example shown, the formula in C5 is:
=MID(B4,FIND("//",B4)+2,LEN(B4)-FIND("//",B4)-1-(RIGHT(B4)="/"))
How this formula works
The core of this formula is the MID function, which extracts the text in a URL starting with the character after “//”, and ending with the character before the trailing slash (“/”):
=MID(url,start,chars)
The url comes straight from B4.
The start is calculated using the FIND function like this:
FIND("//",B4)+2
FIND returns the position of the double slash (“//”) in the URL as a number, so we add 2 in order to start extracting at the next character.
Chars represents the number of characters to extract. We calculate this using the following expression:
LEN(B4)-FIND("//",B4)-1-(RIGHT(B4)="/")
The LEN function calculates the length of the original URL, from which we subtract the position of “//” minus 1. we also use a bit of Boolean logic to conditionally subtract 1 more character:
(RIGHT(B4)="/")
Here the RIGHT function extracts the last character which is compared to “/”. A result of TRUE is evaluated as 1, while a result of FALSE is evaluated as 0.
The Boolean logic is used to avoid additional conditional logic.