+ Reply to Thread
Results 1 to 10 of 10

stripping out only the domain names

  1. #1
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    stripping out only the domain names

    Hi, hopefully a quick question. The attached document is just a sample of thousands of rows of webpages I have listed. What I need is only the root of the domain (don't care about anything after the .com or .net or .org, etc.). Is there an easy way to do this? I tried text-to-columns, seperating each column by the slash, but it didn't work out so clean as some of the domains wound up in different columns.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: stripping out only the domain names

    just quickly
    =LEFT(A1,SEARCH("/",A1,1)-1)
    this will deal with most - NOT the HTTP

    did you want the WWW and the HTTP left in ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,600

    Re: stripping out only the domain names

    You could put this in B1:

    =MID(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("www.",A1)),"","www.")&A1,"https://",""),"http://",""),5,FIND(".",SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("www.",A1)),"","www.")&A1,"https://",""),"http://",""),5)-1)

    and copy down. This will remove https://, http:// and www.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: stripping out only the domain names

    Thanks I'll try that, yes I do want the www and http though

  5. #5
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: stripping out only the domain names

    I tried the formula, unfortunately it doesn't work, I get a #value! as a result

  6. #6
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: stripping out only the domain names

    actually it does work, sorry, Ill mark this as solved, thanks so much!

  7. #7
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: stripping out only the domain names

    As an option:
    1) Ctrl + H and replace all "https://" to nothing
    2) Ctrl + H and replace all "www." to nothing
    3) Select column A, go Data - Text-to-Columns, choose Delimited, as Delimiter choose Other and write "/" in the box

  8. #8
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: stripping out only the domain names

    thanks! That helps a lot too

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: stripping out only the domain names

    A shorter version
    in B1 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    www.advantagetennisacademy.com/programs_tuition.html ? advantagetennisacademy.com
    2
    https://www.imgacademy.com/.../Tennis_Academy_Rate_Card.pdf ? imgacademy.com
    3
    www.mouratoglou.com/application-tennis-school.html ? mouratoglou.com
    4
    www.austintennisacademy.com/tennis-programs/academy/ ? austintennisacademy.com
    5
    www.tbarmtennis.com/academyschool.aspx ? tbarmtennis.com
    6
    www.arizonatennis.org/pages/index.cfm?siteid=5093 ? arizonatennis.org
    7
    usnta.com/tennis/nta-frequently-asked-questions/ ? usnta.com
    8
    usnta.com/tennis/pay-dues/ ? usnta.com
    9
    polotennisclub.com/membership/ ? polotennisclub.com
    10
    www.bta-tennis.com/test/cost/ ? bta-tennis.com
    11
    volleycam.com/the-5-best-rated-american-junior-tennis-academies/ ? volleycam.com
    Sheet: Sheet1
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  10. #10
    Forum Contributor
    Join Date
    12-10-2012
    Location
    Petalu
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: stripping out only the domain names

    Hey guys, ever since I put in the above formulas, all my spreadsheets, even my new ones are pasting with text-to-columns separation, how do I stop this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Easy way to remove domain names from a list
    By ccastell88 in forum Excel General
    Replies: 3
    Last Post: 09-08-2015, 08:36 PM
  2. macro to detect domain names (in url's) ?
    By sami770 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 01-08-2013, 02:34 AM
  3. How do I add http://www. to the beginning of 500 domain names all at once
    By Domaineroo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2012, 04:40 AM
  4. [SOLVED] Stripping-out value from thousands of uniformly composed .jpg file names
    By Krausskopf in forum Excel General
    Replies: 4
    Last Post: 04-10-2012, 05:08 PM
  5. Excel 2003 - How to extract domain names frm email
    By bebas78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2008, 12:57 AM
  6. [SOLVED] Tracking domain names
    By \(Joseph in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2006, 04:25 AM
  7. Help with macro to track domain names
    By \(Joseph in forum Excel General
    Replies: 0
    Last Post: 02-19-2006, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1