This website or its third-party tools use cookies which are necessary to its functioning and required to improve your experience. By clicking the consent button, you agree to allow the site to use, collect and/or store cookies.
The platform this site runs on doesn't have the option to turn off cookies server side, but you can do that in your browser and then return to the site.
I accept
Deny cookies Go Back

Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Aug 03 2013

How To Pull Domains Out Of A List Of URLs In Excel [VIDEO]

extracting domains from URLs in Excel

One thing I like to do when doing a competitive analysis for a client is rifle through their backlinks vis-a-vis their competitors’ and reverse engineer their competitors’ marketing strategies. But sometimes that means extracting text from another column of data to organize your data for maximum pivoting. Since this is a common task I do a lot, I’m going to show you how to pull domains out of a list of URLs using a combo meal of the LEFT and SEARCH functions.

First Batter Up: LEFT Function

The LEFT function follows the following structure:

LEFT( text, [number_of_characters] )

text:ย For this you can either put text in quotation marks inside the formula or point to the cell where the text is. Most people choose the latter. It’s much more scalable.

number_of_characters: So anytime you see something in brackets like this, it means that parameter is optional. In the case of the LEFT function, I think it’s kind of silly to make the number of characters optional. If you leave it out, Excel will just grab one character. Can’t really think of many examples that would be useful, but knock yourself out if you find one. If you have a column of data and the text has the same number of characters in each cell, you’re in luck. You can just enter the number of characters.

Most of us aren’t that lucky though, especially Internet marketers who have to do messy tasks like extract the domain from a group of links from different domains. In such occasions the SEARCH or FIND functions are really helpful.

SEARCH vs FIND Functions in Excel

I like SEARCH over find because it’s not case sensitive (and FIND is). Plus, SEARCH allows you to use wildcards and FIND doesn’t. One of my fave trainers, Mike Girvin, did a YouTube video demonstrating the differences between the two. I can only think of one time I needed to use FIND (because I needed it to be case sensitive). But in this case either will do.

Next Up: SEARCH Function

So what the SEARCH and FIND functions do is return the position of a character you search for. This works swimmingly with the LEFT function because you can find a character’s position and return that for the number of characters you want to extract. If you need to stop at the character before the one you can isolate using the SEARCH function, you can subtract 1 (or however many you need) from the value returned by the SEARCH function. Or you might need to include the character(s) just after it; in that case you would just add 1 to the value.

The SEARCH function follows the following structure:

SEARCH( substring, string, [start_position] )

substring: Pure, unbridled geek speak that means whatever you’re searching for (we’re going to use “/”)

string: Typically the cell this text string is in, though you could enter text as long as you flank it with quotation marks

start_position: This is optional because oftentimes you’ll start looking for your character in the beginning of the string. In our case, the two forward slashes at the beginning of each URL (e.g., http:// and https://) prove to be problematic. So what we’ll need to do is tell Excel to start counting at the 9th character (because http:// has 7 characters and https:// has 8).

Final Formula

When I’m finished the formula looks like this:

=LEFT(B3,SEARCH("/",B3,9))

Note: If you’re doing this in a formatted table, Excel will used structured references instead. That’s what I always do. One other little benefit is you don’t have to double-click the top cell to send the formula down. It gets applied to the column automatically.

I just didn’t use a table for this tutorial because the structured references can make the formulas look unnecessarily complicated when you’re just learning.

Watch It In Action

Still a little squeamish? Download the workbook and check out the video.

Stupid Feedback Noise

In quiet spots in the video I get feedback. The only solution I’ve had any success with is to unplug the cord from my PC, but I forgot to do that. Sorry.

How To Use This Data

In my next post I’ll show you how you can then take this data, pop it into a pivot table, and analyze it to steal glean from all of your competitors’ best ideas.

Learn More

If you want to learn more about filters in Excel, check out myย Annielytics Dashboard Course, a video course that will teach you how to put your data in stilettos and work the pole. ๐Ÿ™‚

Written by Annie ยท Categorized: Excel ยท Tagged: Excel Formulas

Comments

  1. Brian Jensen says

    August 4, 2013 at 11:26 AM

    Great stuff Annie, the video was a really nice touch. I’m looking forward to the next post in the series and appreciate the heads-up on Mike Girvin!

    Reply
    • Annie Cushing says

      August 4, 2013 at 4:41 PM

      Thanks, Brian! Yeah, Mike is awesome. Love his work, and he’s a super nice guy to boot. I’m editing the video for the next post right now. ๐Ÿ™‚

      Reply
  2. cayecaye says

    August 8, 2013 at 11:31 PM

    Hey Annie, as always excel formula rules! ๐Ÿ™‚ I have a question though our team does the same url cleaning daily, however we do it using the TEXT to COLUMNS feature using / as the delimiting factor. After we delimit we concatenate the main URL.. does this process about sums up the formula above?

    Reply
    • Annie Cushing says

      August 9, 2013 at 7:49 AM

      Text to columns also works. It’s just more of a hatchet job approach. But I used it before I learned how to extract text.

      Reply
  3. Peter Traychev says

    October 6, 2013 at 12:09 PM

    Great stuff, thanks ๐Ÿ™‚ I’ve been using =MID(A2,8,SEARCH(“/”,A2,9)-8) but this one is simpler. If there is a way to strip “http://” and “http://www.” part in a single formula that would be great.

    Reply
    • Annie Cushing says

      October 7, 2013 at 12:18 AM

      Hey Peter,

      Mynda Treacy wrote this post on how to extract just the domain: http://www.myonlinetraininghub.com/excel-mid-function. Let me know if you need any help with it!

      Reply
  4. Modesto Siotos says

    October 10, 2013 at 6:33 AM

    Hi Annie,
    Thanks for the tips. One of the example URLs in the spreadsheet is
    http://useconomy.about.com/od/suppl1/tp/BP_Oil_Spill.htm but the returned domain appears as http://useconomy.about.com/ which is a subdomain rather than http://about.com/ which is the actual domain.

    Do you know what’s the quickest way to return the true domain rather than the subdomain? Thanks!

    Reply
    • Annie Cushing says

      October 10, 2013 at 10:08 AM

      Well, technically www is a subdomain too. But since I always do my research at the subdomain level, I keep them.

      Reply
    • Rob James says

      February 20, 2014 at 10:56 AM

      If you want to strip any URL down to the basic domain you end up with a lot of complicated formulae, for example once you’ve remove http://www. the following formula will remove the first subdomain.
      =IFERROR(IF(LEN(SUBSTITUTE(LEFT(C2,LEN(C2)-7),”.”,””))= LEN(LEFT(C2,LEN(C2)-7)),C2,RIGHT(C2,LEN(C2)-SEARCH(“.”,C2,1))),””)

      Reply
  5. Dacey says

    October 29, 2014 at 8:09 AM

    Great finding this from a long time
    Too much Happy

    Reply
    • Annie Cushing says

      November 29, 2014 at 12:00 AM

      Wonderful! Happy to be of help!

      Reply
  6. Brian Jackson says

    June 11, 2015 at 12:26 AM

    Thanks Annie for this great little formula ๐Ÿ™‚ I gave this post a mention in my most recent blog post: http://brianjackson.io/how-i-cleaned-up-a-30000-backlink-profile-in-15-minutes/

    Reply
    • Annie Cushing says

      July 17, 2015 at 11:59 AM

      Thanks! ๐Ÿ™‚

      Reply
  7. Ti. says

    September 8, 2015 at 11:01 AM

    Thank you Annie for sharing knowledge about the extraction of domains through Excel. Thank you Rob for this formula to extract subdomains.

    Reply
    • Annie Cushing says

      September 8, 2015 at 11:23 AM

      My pleasure!

      Reply
  8. Marc-jean Desjardins says

    February 6, 2016 at 6:09 PM

    Is it possible to search a number of unique rows in a single column and have it count the number of occurrences they appear, respectively, on another tab inside the same workbook?

    Ex: Pretend we have two Columns, A and B on sheet2, Column A is the list of 50 states abbreviated, starting at A2 and going down to A51.

    I want column B on sheet2 to count the number of occurrences each state appears in column D on sheet1.

    I don’t want to use a pivot table. Is there a combo of IF/Search/etc… functions that can do this? I want to be able to drag down the box and have it it populate the count(s), so if the States order switch around (in column A on sheet2) it still remains correct.

    Any help is greatly appreciated! ๐Ÿ™‚

    Reply
    • Annie Cushing says

      February 7, 2016 at 5:17 AM

      Oh yeah. These issues are nearly impossible to work through w/o a dummy file. But I’d try wrapping a SEARCH function in a COUNTIF function. I think that’s going to give you your answer. Good luck!

      Reply
  9. Mark says

    June 27, 2016 at 10:51 PM

    Apologies if someone has already mentioned this alternative method or if they linked to another resource that provides instructions, but there is another way to do this very quickly by running multiple “find” and “replace all” queries. Step 1: type in “http://” and replace all with nothing (this will strip all instances of “http://”). Step 2: type in “https://” and replace all with nothing (this will strip all instances of “https://”). Step 3: type in “www.” and replace all with nothing (this will strip all “www” subdomains). Step 4: type in “/*” and replace all with nothing (this will strip all folders beyond the TLD after the first “/” — don’t forget the asterisk). It’s important to follow the steps in the exact order provided.

    Reply
    • Annie Cushing says

      July 1, 2016 at 9:30 AM

      This is not an ideal way to go about getting what you need because it 1) is manual and 2) does permanent damage to your data. It’s much better to use a formula and keep everything intact.

      Reply
  10. Jack says

    August 15, 2017 at 6:27 AM

    Great Post.

    Reply
    • Annie Cushing says

      September 8, 2017 at 5:52 PM

      Thanks.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2023 annielytics.com