Creating IF/Else Formula - Extract Root Domain

[cristopher]

I am trying to take a list of URL links and extract the root domain from each for further comparison.

The issue I am running into is that my files consist of 4 different URLs: http://domain.com/something/; https://domain.com/something/; http://www.domain.com/something/; and https://www.domain.com/something/

I have 2 separate formulas that work, but only 1 does for the "www" URLs and the other for the "non-www" files. See below:

www
=IF(ISERROR(FIND("//www.";A2));MID(A2;FIND(":";A2;4)+3;FIND(":";A2;4)-3);MID(A2;FIND(":";A2;4)+7;FIND("/";A2;9)-FIND(":";A2;4)-7))

non-www
=IF(ISERROR(FIND("//";A2));MID(A2;FIND(":";A2;4)+3;FIND(":";A4;4)-3);MID(A2;FIND(":";A2;4)+3;FIND("/";A2;9)-FIND(":";A2;4)-3))

How can I create 1 single formula that will check all 4 types of URLs and extract the domains?

I have attached my test document to view each formula working separately. Thank you very much if anyone can help.

[robleyd]

Are your domains limited to .com; or do they include generic TLDs; might they have a country code like .co.uk or .com.au?

[cristopher]

Are your domains limited to .com; or do they include generic TLDs; might they have a country code like .co.uk or .com.au?

They are all types of domains, including subdomains. I appreciate your time in helping.

[Zizi64]

Use helper cells for the simplier formulas. When you store the partial results in a some separated cell cells, then you not needed to repeat the parts of the formula in a huge single formula.

[cristopher]

Use helper cells for the simplier formulas. When you store the partial results in a separated cell, then you not needed to repeat the parts of the formula in a huge single formula.

Thanks, so I can do this by using helper cells? I have never used those before so I am somewhat unclear about them.

[Zizi64]

so I can do this by using helper cells? I have never used those before so I am somewhat unclear about them.

formula_with_helper_cells.ods

(14.99 KiB) Downloaded 116 times

[robleyd]

Your use of the term root domain is potentially misleading here; taken literally that might suggest that your expected result for the samples in your first post would all be domain.com or even .com

Can you describe precisely what you are trying to to; for example you might want to strip anything up to and including the first pair of slashes; if the next four characters are www. then strip them; finally strip anything from and including the next slash.