Strip protocol and trailing slash from URL

Strip protocol and trailing slash from URL

Generic formula 

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

Related formulas 

Get domain from email address

Create email address from name

Create email address with name and domain

Get domain name from URL

Get top level domain (TLD)

Get name from email address

Explanation

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

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

Strip protocol and trailing slash from URL

Generic formula 

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

Related formulas 

Get domain from email address

Create email address from name

Create email address with name and domain

Get domain name from URL

Get top level domain (TLD)

Get name from email address

Explanation

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

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