How to extract domain name from a text string

[Nuviman]

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!

[Nuviman]

http://www.domainA.com/some-text-here-123456/domainX.com-12345678
http://www.domainA.com/some-text-here-234567/domainY.org-23456789

[Jonmo1]

Try

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

[JohnTopley]

Try

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

Over-complicated solution!!!

[Nuviman]

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

[Jonmo1]

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'

[Nuviman]

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.

[Tony Valko]

Kind of long but it works...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)),
FIND("-",TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)))-1)

[Nuviman]

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

[Tony Valko]

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

.com
.org
.edu

[Jonmo1]

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

[Nuviman]

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.

[Jonmo1]

Try

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

[Nuviman]

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!!

[Jonmo1]

You're welcome

[ELeGault]

So while I agree

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

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.

Sub StringExtraction()
Dim i As Long 'The Row being evaluated (Set to first row, likely 2)
Dim c As Long 'The Column of your Data
Dim rc As Long 'The column of your results
    'Note - Columns are numbered left to right A being 1
Dim LastRow As Long 'Limit the loop to the last row used in your data
Dim LVal As Long 'How many characters from the left will we return
Dim RVal As Long 'How many characters from the right will we return

    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    i = 2
    c = 1
    rc = 2
    
    'We will loop through your list until it has hit the end
    Do Until i > LastRow
        'Find the LAST Hyphen in your string Less 1 for the position of the hyphen
        LVal = InStrRev(Cells(i, c), "-", -1) - 1
        'Find the LAST Hyphen and the LAST forward slash and get the difference between them Less 1 for the position of the forward slash
        RVal = InStrRev(Cells(i, c), "-", -1) - InStrRev(Cells(i, c), "/", -1) - 1
        
    'Error catching for when your value being evaluated does not contain a Hyphen "-" after a forward slash "/"
    If LVal < 1 Or RVal < 1 Then
        Cells(i, rc).Value = "UnKnown Format"
        Else: Cells(i, rc).Value = Right(Left(Cells(i, c), LVal), RVal)
    End If
        i = i + 1
    Loop

End Sub

Cheers

[Nuviman]

Thank you ELeGault!!

[ELeGault]

You're Welcome - Cheers