Skip to content

Instantly share code, notes, and snippets.

@hansgafriedzal

hansgafriedzal/Excel.md

Created February 12, 2024 03:05
Show Gist options
  • Save hansgafriedzal/1adc654d5e431e561dad645fb766538f to your computer and use it in GitHub Desktop.
Save hansgafriedzal/1adc654d5e431e561dad645fb766538f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Excel.md

Remove domain from URL

=RIGHT(A1, LEN(A1) - SEARCH(CHAR(127), SUBSTITUTE(A1, "/", CHAR(127), 3)))
  • Input: https://gist.github.com/hello/world/
  • Output: hello/world

Description:

  1. SUBSITUTE() replaces the 3rd slash with a dummy i.e., NBSP (non-breaking space) which is the 127th character in the ANSI set.
  2. SEARCH() returns the location of the dummy.
  3. LEN() - SEARCH() returns the length from the location of the dummy until the end of the URL.
  4. RIGHT() returns the substring.

Pre-requisite(s):

  • A1 does not contain the dummy character
  • A1 starts with "http://" or "https://"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment