• http://www.facebook.com/wksears William Sears

    Hi, Annie! Thanks for this. You know we are both hardcore Excel fans. :o)

    Funny I was just working up the formula to extract domains from URLs myself (for the zillionth time) a couple of days ago, and this time I took a second to keep the formula handy in Evernote for the next time I need it. I see our approaches are pretty similar.

    I do it in a Table, so the URL should be in any column titled “Website” (which is the what it is by default in an AWR export).

    This is the way I did it:

    =IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE([@Website],”http://”,””),
    “https://”,””),FIND(“/”,SUBSTITUTE(SUBSTITUTE([@Website],”http://”,””),
    “https://”,””))-1),SUBSTITUTE(SUBSTITUTE([@Website],”http://”,””),”https://”,””))

    This method will work irrespective of whether or not the URL (“Website”) ends in a trailing slash.

    I’m sure there is way to tighten this up. I repeat chunks of it in order to wrap it in the IFERROR that traps for URLs that don’t end in slashes.

    I might make it into a function also, so it’s handy at all times.

    Another way to seriously clean up formulas and make them easier to understand is to use named ranges instead of cell references–but I’ll leave that for another day.

  • http://twitter.com/LeeLKennedy Lee L Kennedy

    Love the post, Annie. I always feel like I’ve accomplished something when I hit the max number of nested functions in Excel :).

    This is the perfect type of post to send to beginner Excelers to show them the power of nested functions. You could even introduce the Name Manager to them to clean up the final formula if you’re feeling adventurous!

  • http://twitter.com/smichaelgriffin Michael Griffin

    Great info! Really wish I had this a few years ago when I (painfully, slowly) started digging into Excel formulas.

  • http://www.annielytics.com/ Annie Cushing

    William! So good to see you here! My formulas bring all the boys to my yard.

    Anywaaay …

    Yeah, I *always* work from a formatted table. I just didn’t with this demo b/c I wanted the formulas to look less intimidating. But w/ your formula, it wouldn’t matter if a URL ended with a trailing slash or not, if you’re just extracting the domain out. It’s a gorgeous formula, nonetheless! That’s the amazing thing about Excel: so many paths can lead to the same outcome.

    My crazy formula for extracting the last directory though won’t work if there’s not a trailing slash. That’s how it detects it’s a directory. I thought about trying to compensate for it, but my brain was mush by that point.

  • http://www.annielytics.com/ Annie Cushing

    Me too!

  • http://www.annielytics.com/ Annie Cushing

    Yeah, I name my ranges for things like lookups to simplify the formula. Outside of those I usually just go grab what I need. I should probably get better about that.

  • Doc Sheldon

    Awesome stuff, Annie! I took one look at your formula at the beginning, groaned and backed away slowly. But you explained it beautifully! Who says you can’t teach an old dog new tricks? Thanks for sharing the wisdom!

  • http://www.annielytics.com/ Annie Cushing

    You know, that was my hesitation in starting off with a beastly formula. But if you download the Excel file and see the individual steps, it’s pretty simple. In any case, glad it helped in some way, Doc! :)

  • http://www.ezmaal.com/ hyderali

    Hi Annie,

    Thanks for this excel tips. I’m a n00b in excel & finding little bit hard the one you shown in the post. But I’ll definitely try it & see where I’m in the world of excel.

  • http://twitter.com/MaryKayLofurno Mary Kay Lofurno

    Hi Anne,

    Thanks for the great article, its a keeper. I have been trying to buckle down in excel and bought an elearning course on advanced excel. My goal is to go through all your articles that I have bookmarked once I finished the course.

  • http://www.annielytics.com/ Annie Cushing

    Yeah, like I said in the post, even if you don’t understand what I did in those formulas, the principle of using helper cells still stands. Try it with simpler formulas, and build your confidence. Then go big.

  • http://www.annielytics.com/ Annie Cushing

    Good luck, Mary Kay! I think time spent learning Excel is time well spent.

  • http://www.annielytics.com/ Annie Cushing

    Yeah, good point! I’ve just been using the IF(ISERROR()) for so long. Some habits are hard to break …

  • http://www.annielytics.com/ Annie Cushing

    I store formulas by appending them with the single quote, but I don’t use them when I’m sculpting my formulas. Instead I use F9 to evaluate portions of a complex formula to convert them to arrays. You just have to remember to press Ctrl-Z to change it back to a formula.

  • http://twitter.com/dergal Gerry White

    for domains, seotools for the win! – niels bosma – I know that the point of the article was to teach people how to do it, but sometimes you got to be lazy! – Love using tables and pivots, so many cool functions within Excel no one knows!

  • http://www.annielytics.com/ Annie Cushing

    Very true!