• Home
  • About
  • Insights Hub
    • New Posts
    • Technical SEO
    • General Discussion
    • View All Forums
  • Blog
  • Contact
    • Hub
      New posts Search forums
    • What's new
      New posts New profile posts Latest activity
    • Members
      Current visitors New profile posts Search profile posts
    Log in Register
    What's new Search

    Search

    By:
    Advanced search…
    • New posts
    • Search forums
    Menu
    Log in

    Register

    Install the app
    • Home
      • Hub
      • Technical SEO
      You are using an out of date browser. It may not display this or other websites correctly.
      You should upgrade or use an alternative browser.

      Strip URL to Root Domain - Excel & Google Sheets Formula

      • Thread starter Chris
      • Start date Sep 12, 2021
      • Sep 12, 2021
      If you are working with large lists of URLs that you need to strip down to the domain root, then this nifty Excel and Google Sheets formula might just be the time-saving tip that you're looking for.

      Update:
      The following formula will strip away protocols (https://,http://,www.) and the URI path. Use this instead.

      Code:
      =MID(SUBSTITUTE(A2,"www.",""),SEARCH(":",A2)+3,SEARCH("/",SUBSTITUTE(A2,"www.",""),9)-SEARCH(":",A2)-3)

      Instructions:
      Step 1
      , Assuming the list of URLs in column A, line 2. Paste the following formula into a cell of your choice. I generally create a new column to the right. For example B2. Drag the formula down to cover all the URLs listed on column A or use autofill.

      Code:
          =LEFT(A2,FIND("/",A2,9))

      Step 2, Next step is to strip away the protocols (https://,http://,www.). Leaving you with the first character of the domain name. For this, we are going to use the completed formula as covered in step 1. Again I generally create another new column. Let us use column C for this action.

      Paste the formula below into C2 and drag it down to the end of the list of URLs.

      Code:
      =MID(A2,FIND("//",B2)+2,LEN(B2)-FIND("//",B2)-1-(RIGHT(B2)="/"))

      That's it, you should have a complete list of URLs stripped right back down to the domain name root. Stripping away the protocols (https://,http://,www.) and the URI path (anything after domain.com/uri-here).

      Comment below if you have a simpler method or formula. I would be keen to amalgamate this into a single formula.
       
      Click to expand...
      C
      Written by

      Chris

      Administrator
      Staff member
      • Messages
        51
      • Reaction score
        0
      • Points
        6
      You must log in or register to reply here.
      Share:
      Facebook Twitter Reddit Pinterest Tumblr WhatsApp Email Share Link

      New Posts

      • vogeldm
        SEO and Digital Marketing services
        • Started by vogeldm
        • Apr 22, 2022
        • Replies: 0
        Technical SEO
      • C
        Dropped .UK Domains 11th April 2022
        • Started by Chris
        • Apr 11, 2022
        • Replies: 0
        General Discussion
      • C
        Google Sheets Regex for excluding subfolders
        • Started by Chris
        • Oct 12, 2021
        • Replies: 0
        Technical SEO
      • C
        Modify or Deploy Meta Viewport - Google Tag Manager
        • Started by Chris
        • Sep 24, 2021
        • Replies: 0
        Technical SEO
      • C
        Open-source tools & projects that use Lighthouse
        • Started by Chris
        • Sep 24, 2021
        • Replies: 0
        Technical SEO
      • C
        How to Force HTTPS using .htaccess (cPanel)
        • Started by Chris
        • Sep 24, 2021
        • Replies: 0
        Technical SEO
      • C
        Canonical Tag Override using Google Tag Manager
        • Started by Chris
        • Sep 24, 2021
        • Replies: 0
        Technical SEO
      • C
        Websites to use DNS Prefetching & Prefetching
        • Started by Chris
        • Sep 24, 2021
        • Replies: 0
        Technical SEO
      • Hub
      • Technical SEO

    Join the Community

    Chris Lever is a freelance SEO Consultant availible for Hire. Join the growing SEO Community Forum to learn new skills.

    Join Us

    Need Help?

    • BB Code
    • Cookie Policy
    • Terms of Use
    • Privacy Policy
    • HTML Sitemap

    Learn More

    • About
    • Availability
    • Popular Posts
    • Projects & Scripts
    • Contact

    Get in Touch

    Chris Lever, Manchester, United Kingdom
    info@chrisleverseo.com
    +44 0161 883 2249

    © 2021 Chris Lever SEO. All rights reserved. Powered by Xenforo

    • This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
      By continuing to use this site, you are consenting to our use of cookies.
      Accept Learn more…