Strip protocol and trailing slash from URL

To remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formual based on the MID, FIND, and LEN functions. In the example shown, the formula in C5 is:

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

Explanation

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 B5.

The start is calculated using the FIND function like this:

FIND("//",B5)+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(B5)-FIND("//",B5)-1-(RIGHT(B5)="/")

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(B5)="/")

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.