Explanation
In this example, the goal is to remove the protocol from a list of URLs.
To remove the protocol from a URL, we need to remove the first part of the URL.
In the current version of Excel, the easiest way to do this is with the TEXTAFTER function.
In older versions of Excel, you could use a formula based on the MID and FIND functions.
Both options are explained below.
TEXTAFTER function
The TEXTAFTER function returns the text that occursaftera given delimiter.
The result is the original URL without the protocol.
Want to learn more about TEXTAFTER?
Check out our guide:How to use the TEXTAFTER function.
With the text “https://www.domain.com” in cell B5, FIND returns 9.
Using the LEN function like this is a shortcut, designed to simplify the formula.
LEN will return 22, which is greater than the number of characters that remain.
However, whennum_charsexceeds the remaining string length, MID will simply extract all remaining characters.
The final result is “www.domain.com”.
For example, =MID(“apple”,2,3) returns “ppl”.
For example, =RIGHT(“apple”,3) returns “ple”.
LEN will also count characters in numbers, but number formatting is not included.
When the text is not found, FIND returns a #VALUE error.
