+ Reply to Thread
Results 1 to 20 of 20

How to extract domain name from a text string

  1. #1
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Question How to extract domain name from a text string

    Hi guys,

    I would really appreciate it if anyone could help me with this task.

    I have one column of data:
    http://www.domainA.com/some-text-her...X.com-12345678
    http://www.domainA.com/some-text-her...Y.org-23456789
    ....

    Thereís about 1000 lines of this data with different domain names in the end of each text string.

    Is there any easy way to extract domain name from these strings? In this example I need to have the output like this:
    domainX.com
    domainY.org

    Could please anyone help me with the best approach to accomplish this task?

    Thank you in advance!

  2. #2
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Please Login or Register  to view this content.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to extract domain name from a text string

    Try

    =REPLACE(LEFT(A1,FIND("/",A1,FIND(".",A1))-1),1,FIND(".",A1),"")
    Last edited by Jonmo1; 09-23-2016 at 09:43 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: How to extract domain name from a text string

    Try

    =LEFT(SUBSTITUTE(A2,"http://www.",""),FIND("/",SUBSTITUTE(A2,"http://www.",""),1)-1)

    Over-complicated solution!!!

  5. #5
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Thanks, but I need to note that I don't need to extract the domain name which is constant and is in the beginning of each string http://www.domainA.com
    I need to extract these:
    domainX.com
    domainY.org

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to extract domain name from a text string

    I think you'll need to provide a larger set of examples that covers all possible formats of the original url string.
    And show expected results for each.

    Can you attach a sample Excel File, click 'Go Advanced' - 'Manage attatchments'

  7. #7
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Quote Originally Posted by Jonmo1 View Post
    I think you'll need to provide a larger set of examples that covers all possible formats of the original url string.
    And show expected results for each.

    Can you attach a sample Excel File, click 'Go Advanced' - 'Manage attatchments'
    Sure. Please find attached the requested file.
    Attached Files Attached Files

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to extract domain name from a text string

    Kind of long but it works...

    =LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)),
    FIND("-",TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)))-1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Quote Originally Posted by Tony Valko View Post
    Kind of long but it works...

    =LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)),
    FIND("-",TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)))-1)
    Thanks a lot!
    Is it possible to make it work in case a domain has a slash symbol?
    Example: decorating-homes.com

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to extract domain name from a text string

    Does the domain name always end in dot followed by 3 characters?

    .com
    .org
    .edu

  11. #11
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Quote Originally Posted by Tony Valko View Post
    Does the domain name always end in dot followed by 3 characters?

    .com
    .org
    .edu
    Mostly yes.

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to extract domain name from a text string

    "Mostly" isn't good enough!

    It has to be ALL or nothing.

  13. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to extract domain name from a text string

    Is the first part of the URL always the same?
    h ttp://www.mythemedetector.com/mts_newspaper-wordpress-website-template-101762/

  14. #14
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Quote Originally Posted by Jonmo1 View Post
    Is the first part of the URL always the same?
    h ttp://www.mythemedetector.com/mts_newspaper-wordpress-website-template-101762/
    Yes, it's always the same.

  15. #15
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to extract domain name from a text string

    Try

    =LEFT(REPLACE(A1,1,79,""),FIND("-",REPLACE(A1,1,79,""),FIND(".",REPLACE(A1,1,79,"")))-1)

  16. #16
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Quote Originally Posted by Jonmo1 View Post
    Try

    =LEFT(REPLACE(A1,1,79,""),FIND("-",REPLACE(A1,1,79,""),FIND(".",REPLACE(A1,1,79,"")))-1)
    This works awesome!! Thank you so much Jonmo1!!

  17. #17
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to extract domain name from a text string

    You're welcome

  18. #18
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    890

    Re: How to extract domain name from a text string

    So while I agree
    Please Login or Register  to view this content.
    Does the trick... I prefer to never lock down string counts when it is based on variables throughout the code. The above will work if you 100% of the time will maintain your format.

    Here is the process in VB as well - personally I like VB because there is nothing calculating in my workbooks when I open it - just the results pushed when desired -
    You will need to modify the code as I am sure your data doesnt live in column A and start on 2... likely something you are working in...although if it is text like you provided you could just paste it in A2 and not have to modify the code below.

    Please Login or Register  to view this content.
    Cheers
    -If you think you are done, Start over - ELeGault

  19. #19
    Registered User
    Join Date
    07-17-2014
    Location
    Europe
    MS-Off Ver
    2010
    Posts
    25

    Re: How to extract domain name from a text string

    Thank you ELeGault!!

  20. #20
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    890

    Re: How to extract domain name from a text string

    You're Welcome - Cheers

+ 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. Formula Extract hostname only from the domain
    By flameexcel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2014, 09:03 AM
  2. Extract a domain name from an email address
    By kpratico in forum Excel General
    Replies: 4
    Last Post: 01-01-2013, 08:43 PM
  3. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  4. [SOLVED] extract text string when you only know the last three letters of that string
    By alison0edwards in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-12-2012, 01:20 PM
  5. Extract domain from URL?
    By gnome_core in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2012, 02:52 AM
  6. Extract Domain from URL
    By joh in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-21-2010, 12:05 AM
  7. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 AM

Tags for this Thread

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