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.
In the example shown, the formula in C5 is:
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 (“/”):
The url comes straight from B4.
The start is calculated using the FIND function like this:
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:
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:
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.