• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

I need a qucik will to extract domain names from list of URL

Hello,

I have a huge list of URLs from a log file. I want  quick way to convert there url to a list of domain name. Notice that  I have this list in text file and Excel

If Macro can do the job that will be great

Best Regards

Zaki
0
zaki100
Asked:
zaki100
1 Solution
 
ElKermCommented:
Open the list
In the next column type use the formula:

=mid(text;start number;end number)

Where 'text' is your cell which contains the url
Where number is the starting letter from where it should take the characters and where it should end.

If you have for instance:

Cell A1 = http://www.domainname.com

Than B1 would hold the formula:

=MID(A1;8;1000)

(I took 1000 to make sure you have all characters).

This would result in www.domainname.com

You can also start later and remove the www but playing with the starting character. The 8 in the formula.

I hope this helps.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You probably have differnt combiation of
http://www.ABC.com
https://www.ABC.com
http://ABC.com
http://www.ABC.com/Sles...
https://www.ABC.com/....
http://ABC.com/....

In all case you probably all you want is ABC.com

Is this correct?

I will suggest linking txt or excel file to Access, youe have a table with one field at lease

tblData
============
[MyURL]

Makw a qyery:

Select fnDomain([MyURL) As Doman From tblData

Anf in a moduel have

Function fnDomain(strURL As String) As String
    fnDomain="Test.Domain"
End Function

This will be your structure. Nex we will add code to this function to accomodate any condition there might exist.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Function fnDomain(strURL As String) As String

Dim strBegining As string
Dim strDomaiType As string

' check to see if strURL as hht: or https
If Left(strURL,7)="http://"  Then
   strBegining = Left(strURL,7)
ElseIF Left(strURL87)="https:// Then
     strBegining = Left(strURL,8)
End If  ' expand to include any other conditions there might be

If InStr(strURL,"www.")>0"  Then
   strBegining = strBegining & "www."
End If
If Instr(strURL,".com")> 0 Then

strDomaiType =".com"
 
.
.
;expand  this
   fnDomain="Test.Domain"
End Function

brb
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Mike EghtebasDatabase and Application DeveloperCommented:
Function fnDomain(strURL As String) 

Dim strBegining As string
Dim strDomaiType As string
Dim strTemp As string

' check to see if strURL as hht: or https
If Left(strURL,7)="http://"  Then
  strBegining = Left(strURL,7)
ElseIF Left(strURL87)="https:// Then
    strBegining = Left(strURL,8)
End If  ' expand to include any other conditions there might be

If InStr(strURL,"www.")>0"  Then
  strBegining = strBegining & "www."
End If

If Instr(strURL,".com")> 0 Then
    strDomaiType =".com"
ElseIf Instr(strURL,".net")> 0 Then
    strDomaiType =".net"
ElseIf Instr(strURL,".edu")> 0 Then
    strDomaiType =".edu"
End if
 strTemp=mid(strUTL,Len(strBegining)+1,inst(str, strDomaiType)-1)

   fnDomain= strBegining & strTemp & strDomaiType
End Function
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
This part:

If Instr(strURL,".com")> 0 Then
    strDomaiType =".com"
ElseIf Instr(strURL,".net")> 0 Then
    strDomaiType =".net"
ElseIf Instr(strURL,".edu")> 0 Then
    strDomaiType =".edu"
end if

could modified to autatically detect any domain type later.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Add this UDF to a general code module:

Public Function GetDomainName(ByVal URL As String) As String

   With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "^(?:http:\/\/|https:\/\/)*([^.]*[\.])*([^.]+\.(?:co\.uk|com|net|org|in)).*$"
      GetDomainName = .Replace(URL, "$2")
   End With

End Function

Use this function in a cell to get the domain name of a URL:

   =GetDomainName(A1)

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

The above function finds .com, .org, .net, .in and co.uk domain names. You can add as many suffixes as you want to the list. Separate them with pipes.

Kevin
0
 
zaki100Author Commented:
Good script zorvek

Actually I want this script to go through all rows and convert url automatically because I have huge number of URLs (1 million+)

Any help

Thanks

Zaki
0
 
zorvek (Kevin Jones)ConsultantCommented:
Use this macro in conjunction with the above code. Select the cells to be edited and it will do the work in place.

Public Sub ConvertURLs()
   Dim Cell As Range
   For Each Cell In Selection
      Cell = GetDomainName(Cell.Value)
   Next Cell
End Sub

Kevin
0
 
premierncsCommented:
Is there anyway to just count the number of "." from right to left and return just what's after the 2nd to last "."?
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now