Both approaches are explained below.
TEXTAFTER with TEXTBEFORE
The formula in the worksheet shown uses theTEXTAFTERandTEXTBEFOREfunctions to extract domain names from URLs.
With the text “https://exceljet.net/formulas/" in cell B5, TEXTAFTER returns “exceljet.net/formulas/”.
TEXTBEFORE locates the single forward slash “/” and returns all previous text.
The final result is the domain “exceljet.net”.
with an empty string ("").
If “www.”
is not found, SUBSTITUTE returns the original result.
The LEFT function then extracts 21 characters from the URL, starting at the left.
The result is the domain name with a trailing slash, “https://exceljet.net/".
“https://") in place.
This is actually a hack to keep things simple.
Using LEN to providenum_charsis a simple way to give MID a number that is always large enough.
For example, =LEFT(“apple”,3) returns “app”.
When the text is not found, FIND returns a #VALUE error.
