Solved

Root domain formula

Forum|Forum|6 years ago
November 16, 2018
15 replies
111 views

Jen_Baguhin
New Participant
3 replies

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

Best answer by Joachim_Brindea

This removes subdomains, folders, etc and doesn't output anything if there is no url.

IF({url}="","",REGEX_EXTRACT( {url}, '^(?:https?:\\/\\/)?(?:[^@\n]+@)?(?:www\\.)?(?:[^.]+\\.)?([^:\\/\n?]+\\.[^:\\/\n?]+)'))

+10

Helmi
Known Participant
19 replies
Forum|Forum|6 years ago
November 16, 2018

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.

+18

Jeremy_Oglesby
Inspiring
1691 replies
Forum|Forum|6 years ago
November 16, 2018

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)
  )
)

+10

Helmi
Known Participant
19 replies
Forum|Forum|6 years ago
November 17, 2018

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)
  )
)

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

Jen_Baguhin
Author
New Participant
3 replies
Forum|Forum|6 years ago
November 17, 2018

WORKED! :slightly_smiling_face: thank you!

Kane_Jamison
New Participant
1 reply
Forum|Forum|6 years ago
August 19, 2019

Here’s what we use to turn raw urls into domains (retaining subdomain if there is one):

LEFT(SUBSTITUTE(SUBSTITUTE({URL cell},“http://”,""),“https://”,""),FIND("/",SUBSTITUTE(SUBSTITUTE({URL cell},“http://”,""),“https://”,""))-1)

This removes http(s)://, then looks for the first forward slash, and deletes anything after that.

It could fail if you have a slash-less URL such as domain:8080 or something like a parameter occurring without a trailing slash on the TLD.

Brad_Siefert
New Participant
1 reply
Forum|Forum|5 years ago
June 19, 2020

I came up with a much more robust, but also sorta dumb formula because I couldn’t get @Kane_Jamison’s solution to work.

IF(
  LEFT(
    SUBSTITUTE(
      SUBSTITUTE(  
        SUBSTITUTE({URL},"https://",""),"http://",""
      ),"www.",""
    ),
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(  
          SUBSTITUTE({URL},"https://",""),"http://",""
        ),"www.",""
      )
    )-1
  ),
  LEFT(
    SUBSTITUTE(
      SUBSTITUTE(  
        SUBSTITUTE({URL},"https://",""),"http://",""
      ),"www.",""
    ),
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(  
          SUBSTITUTE({URL},"https://",""),"http://",""
        ),"www.",""
      )
    )-1
  ),
  SUBSTITUTE(
    SUBSTITUTE(  
      SUBSTITUTE({URL},"https://",""),"http://",""
    ),"www.",""
  )
)

Yonathan_Cohen
Inspiring
6 replies
Forum|Forum|5 years ago
August 25, 2020

My formula works perfectly, use it without moderation, just REPLACE “main domain name” by the URL_column_name that you want to clear:

LEFT(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""),
  IF(
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""))=0, 
    LEN(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", "")), 
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""))-1))

airtable formula to extract domain name from URL airtable

Yonathan_Cohen
Inspiring
6 replies
Forum|Forum|4 years ago
July 27, 2021

My formula works perfectly, use it without moderation, just REPLACE “main domain name” by the URL_column_name that you want to clear:

LEFT(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""),
  IF(
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""))=0, 
    LEN(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", "")), 
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""))-1))

airtable formula to extract domain name from URL airtable

This is a bit more advanced, it will also remove the first part of such an URL: en.website.com
For example:
=>https://www.en.orchestrasales.com/home/view/
will be turned into “orchestrasales.com”

LEFT(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""),
  IF(
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""))=0, 
    LEN(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", "")), 
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""))-1))

Brian_Moran
New Participant
3 replies
Forum|Forum|3 years ago
May 16, 2022

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)
  )
)

Hi @Jeremy_Oglesby I don’t suppose I could ask you to add to this?

I have multiple top level domains in my data set e.g .com .org .ie etc etc

How can I modify the tld seach string below to pick up multiple tld values?

IF(
SEARCH(‘com/’, URL),
LEN(URL) -

Would really appreciate any help

Thanks,

Brian

+18

Jeremy_Oglesby
Inspiring
1691 replies
Forum|Forum|3 years ago
May 18, 2022

Hi @Jeremy_Oglesby I don’t suppose I could ask you to add to this?

I have multiple top level domains in my data set e.g .com .org .ie etc etc

How can I modify the tld seach string below to pick up multiple tld values?

IF(
SEARCH(‘com/’, URL),
LEN(URL) -

Would really appreciate any help

Thanks,

Brian

Hey, @Brian_Moran

Sorry, I’m just seeing this. Luckily, in the 4 years since I wrote that formula you are referencing, Airtable has added some REGEX functions that should help with that.

Perhaps try replacing the entire SEARCH('com/', URL) function with this:

REGEX(URL, "[com|org|net|ie|io]/")

If that works, you can continue to add any other tld’s you might need inside those brackets, separated by a ‘pipe’ character |.

Brian_Moran
New Participant
3 replies
Forum|Forum|3 years ago
May 18, 2022

Hey, @Brian_Moran

Sorry, I’m just seeing this. Luckily, in the 4 years since I wrote that formula you are referencing, Airtable has added some REGEX functions that should help with that.

Perhaps try replacing the entire SEARCH('com/', URL) function with this:

REGEX(URL, "[com|org|net|ie|io]/")

If that works, you can continue to add any other tld’s you might need inside those brackets, separated by a ‘pipe’ character |.

Thanks Jeremy! I’ll give that a try and report back!

Chris_Lally
New Participant
1 reply
Forum|Forum|3 years ago
May 24, 2022

I ended up needing to just get rid of the www. or https:// and such AND a trailing “/” IF there was one, hope it helps!

LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(URL, "http://",""), "https://",""), "www.", ""),
IF(RIGHT(URL,1)="/",
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(URL, "http://",""), "https://",""), "www.", ""))-1,
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(URL, "http://",""), "https://",""), "www.", ""))
))

Dana_Berkowitz
New Participant
3 replies
Forum|Forum|3 years ago
July 11, 2022

This is a bit more advanced, it will also remove the first part of such an URL: en.website.com
For example:
=>https://www.en.orchestrasales.com/home/view/
will be turned into “orchestrasales.com”

LEFT(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""),
  IF(
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""))=0, 
    LEN(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", "")), 
    FIND("/",
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
              SUBSTITUTE(
                  SUBSTITUTE({main domain name}, "http://",""), "https://",""), "www.", ""), "fr.", ""), "en.", ""))-1))

Thanks! I used this and it worked great. It saved me a bunch of time to look up something someone shared instead of reinventing it myself.

Joachim_Brindea
Inspiring
22 replies
Answer
Forum|Forum|2 years ago
March 16, 2023

This removes subdomains, folders, etc and doesn't output anything if there is no url.

IF({url}="","",REGEX_EXTRACT( {url}, '^(?:https?:\\/\\/)?(?:[^@\n]+@)?(?:www\\.)?(?:[^.]+\\.)?([^:\\/\n?]+\\.[^:\\/\n?]+)'))

gratitude911
New Participant
1 reply
Forum|Forum|1 year ago
December 15, 2023

This removes subdomains, folders, etc and doesn't output anything if there is no url.

IF({url}="","",REGEX_EXTRACT( {url}, '^(?:https?:\\/\\/)?(?:[^@\n]+@)?(?:www\\.)?(?:[^.]+\\.)?([^:\\/\n?]+\\.[^:\\/\n?]+)'))

This worked very well, thank you

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded