Extracting part of a cell to return in another cell

Devon Eddy · 10/29/20

I'm trying to come up with a formula which will take the website URL in one cell, extract what's called the root domain (website.com) without www. or https:// or any url string after the .com

Here's a couple of examples of the value I have in the cell and what I would like it to return in a horizontal cell:

http://www.internationalrealestatedirectory.com/country/united_states/builders.htm -> internationalrealestatedirectory.com

https://home-directory.biz -> home-directory.biz

http://www.regionaldirectory.us/ -> regionaldirectory.us

Is this something that would be possible given, that there are multiple domain extensions and multiple scenarios that could be before the beginning of the domain? If so, help with the formula would be MUCH appreciated.

Andrée Starå · 10/30/20

Hi @Devon Eddy

Try something like this.

Start with adding three so-called helper columns.

You can call them, Position, Contains, and New

Add the following formulas (the URL column is the one that includes you're starting URL)

Position

=FIND("/"; Contains@row)

Contains

=IF(CONTAINS("http://www."; URL@row); SUBSTITUTE(URL@row; "http://www."; ""); IF(CONTAINS("https://"; URL@row); SUBSTITUTE(URL@row; "https://"; "")))

New

=LEFT(Contains@row; Find@row - 1)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

This URL http://www.internationalrealestatedirectory.com/country/united_states/builders.htm would show as internationalrealestatedirectory.com after the conversion.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 10/30/20