256 Kilobytes

Answers in Spreadsheets | By Some Guy

Published 11 months agoSun, 02 Dec 2018 03:43:26 -0800 | Last update 11 months agoSun, 02 Dec 2018 13:03:41 -0800

139 views, 0 RAMs, and 1 comment

Tags: REGEX, URLs, Domains, Subdomains

Profile Photo - Some Guy Some Guy
🗎 3224 🗨 41 🐏 31

I have a bunch of URLs and domains and have a few tasks that seem like they could be done quickly with Google Sheets:

  1. Extract a domain from a subdomain. Ex:
    • dogs.wordpress.com --> wordpress.com
  2. Convert a domain to a URL. Ex:
  3. Convert a URL to a domain. Ex:

I've tried basic concatenation ("https://www."&A1) and basic removal of strings with SUBSTITUTE, but these only work for the most basic cases. If you have a bunch of URLs/domains, this causes some to convert incorrectly.

Download more RAM. 🐏 ⨉ 0 Posted by Some Guy 11 months ago

Edit History

• [2018-12-02 3:43 PST] Some Guy (11 months ago)
🕓 Posted at 02 December, 2018 03:43 AM PST

This is a patsy account used by the 256 Kilobytes staff to seed content. 

Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
🗎 198 🗨 984 🐏 299
Site Owner

Solutions Using Google Sheets

Extract domain from subdomain

Example input: howtogoaround.wordpress.com

  • =RIGHT(REGEXEXTRACT(D25, "\..*"), LEN(REGEXEXTRACT(D25, "\..*"))-1)

Something something check if subdomain / count numberof period (JK THIS IS IRRELEVANT GARBAGE):

  • =LEN(B120)-LEN(SUBSTITUTE(B120,".",""))

Convert domain to URL



* Basically works

* Verbose with error handling

# Regex to convert URL to domain
* These only work if the URL
--> ends with a slash
--> Includes http or https

Extract the domain with www. if it is present in a URL:

Omit www. and get a pure domain name:
=IF(ISERROR(FIND("//www.",A2)), MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))

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

Download more RAM. 🐏 ⨉ 0 Posted by August R. Garcia 11 months ago

Edit History

• [2018-12-02 3:47 PST] August R. Garcia (11 months ago)
🕓 Posted at 02 December, 2018 03:47 AM PST

Sir, I can do you a nice SEO.

Post a New Comment

To leave a comment, login to your account or create an account.

Do you like having a good time?

Read Quality Articles

Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

View Articles →

Argue with People on the Internet

Use your account to explain why people are wrong on the Internet forum.

View Forum →

Vandalize the Wiki

Or don't. I'm not your dad.

View Wiki →

Ask and/or Answer Questions

If someone asks a terrible question, post a LMGTFY link.

View Answers →

Make Some Money

Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

Register an Account
You can also login to an existing account or recover your password. All use of this site is subject to terms outlined in the terms of service and privacy policy.