Root domain formula


#1

Is there any way to change different versions of domains to its root domains?

example:

If we add

Column 1
https://www.domain.com/
https://domain.com/
https://www.domain.com
https://domain.com
http://www.domain.com/
http://domain.com/
http://www.domain.com
http://domain.com

Column 2 Result in all rows of above
domain.com


#2

I’m not a big formula pro yet but I tried it out and it seems to work:

MID(
  URL, 
  IF(
    SEARCH('//www.', URL),
    SEARCH('//www.', URL) + 6,
    IF(
      SEARCH('//', URL),
      SEARCH('//', URL) + 2,
      IF(
        SEARCH('www.', URL),
        SEARCH('www.', URL) + 4,
        1
      )
    )
  ),
  LEN(URL)
)

here’s what it looks for me when testing:

I’m sure there are more efficient ways to do this and there might be some things left over that you don’t want but maybe a good starter still.


#3

To add one little bit to @Helmi’s solution, this will also catch the ‘/’ at the end if it’s there:

MID(
  URL, 
  IF(
    SEARCH('//www.', URL),
    SEARCH('//www.', URL) + 6,
    IF(
      SEARCH('//', URL),
      SEARCH('//', URL) + 2,
      IF(
        SEARCH('www.', URL),
        SEARCH('www.', URL) + 4,
        1
      )
    )
  ),
  IF(
    SEARCH('com/', URL),
    LEN(URL) - 
    IF(
      SEARCH('//www.', URL),
      SEARCH('//www.', URL) + 6,
      IF(
        SEARCH('//', URL),
        SEARCH('//', URL) + 2,
        IF(
          SEARCH('www.', URL),
          SEARCH('www.', URL) + 4,
          1
        )
      )
    ),
    LEN(URL)
  )
)

#4

hmm but then only if it’s .com, right? I’d better not rely on that. But good idea trying to catch that / - I was also thinking about trying to remove paths in the URL


#5

WORKED! :slight_smile: thank you!