Extract domain names out of URLs

Hi, I have a list of URLs and I would like to find a way to extract
the domain name from the URL.

The URLs can be prefixed with www in some cases and I'd also like to
strip that off.

So, for the following URLs
http://www.wiseclerk.com/group-news/tag/libor
http://www.wiseclerk.com/group-news/2008/04/
http://www.wiseclerk.com/group-news/
http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
sports
http://www.techdirt.com/articles/20080408/223932792.shtml
http://www.techdirt.com/articles/20080408/223932792.shtml
http://www.techdirt.com/
http://www.techdirt.com/
http://techdirt.com/index.php
http://techdirt.com/blog.php?tag=loans



I'd like to extract

wiseclerk.com
tiscali.co.uk
techdirt.com

I'm struggling to find the correct algorithm to locate the starting
point (after the http:// or after the http://www.) and the ending
point (the first /) for my Mid function.

Any help appreciated.

I'm thinking that I might have to do a find/search function for either
of the two starting strings and then have the "max" value (for the
longer of the two strings) but that's a lot of extra columns in my
spreadsheet.

Is there perhaps a better way to do this?
0
MPBrede (29)
4/20/2008 5:17:08 PM
excel 39879 articles. 2 followers. Follow

28 Replies
2414 Views

Similar Articles

[PageSpeed] 34

I think this formula does what you want and handles all the various 
possibilities...

=IF(LEFT(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUTE(A1,"http://","")&"/")-1),4)="www.",MID(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUTE(A1,"http://","")&"/")-1),5,256),LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUTE(A1,"http://","")&"/")-1))

Rick


"MikeB" <MPBrede@gmail.com> wrote in message 
news:fadd409d-aee3-4b9f-ae9e-4ce3501963aa@24g2000hsh.googlegroups.com...
> Hi, I have a list of URLs and I would like to find a way to extract
> the domain name from the URL.
>
> The URLs can be prefixed with www in some cases and I'd also like to
> strip that off.
>
> So, for the following URLs
> http://www.wiseclerk.com/group-news/tag/libor
> http://www.wiseclerk.com/group-news/2008/04/
> http://www.wiseclerk.com/group-news/
> http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
> sports
> http://www.techdirt.com/articles/20080408/223932792.shtml
> http://www.techdirt.com/articles/20080408/223932792.shtml
> http://www.techdirt.com/
> http://www.techdirt.com/
> http://techdirt.com/index.php
> http://techdirt.com/blog.php?tag=loans
>
>
>
> I'd like to extract
>
> wiseclerk.com
> tiscali.co.uk
> techdirt.com
>
> I'm struggling to find the correct algorithm to locate the starting
> point (after the http:// or after the http://www.) and the ending
> point (the first /) for my Mid function.
>
> Any help appreciated.
>
> I'm thinking that I might have to do a find/search function for either
> of the two starting strings and then have the "max" value (for the
> longer of the two strings) but that's a lot of extra columns in my
> spreadsheet.
>
> Is there perhaps a better way to do this? 

0
4/20/2008 5:52:06 PM
Mike

Try something like

=IF(ISERR(FIND("www",A1,1)),MID(A1,8,FIND("/",A1,8)-8),MID(A1,12,FIND("/",A1,12)-12))

this works on the theory that there is a trailing / after the domain 
somewhere, it will need amending if there is not, e.g 
http://excelusergroup.org

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"MikeB" <MPBrede@gmail.com> wrote in message 
news:fadd409d-aee3-4b9f-ae9e-4ce3501963aa@24g2000hsh.googlegroups.com...
> Hi, I have a list of URLs and I would like to find a way to extract
> the domain name from the URL.
>
> The URLs can be prefixed with www in some cases and I'd also like to
> strip that off.
>
> So, for the following URLs
> http://www.wiseclerk.com/group-news/tag/libor
> http://www.wiseclerk.com/group-news/2008/04/
> http://www.wiseclerk.com/group-news/
> http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
> sports
> http://www.techdirt.com/articles/20080408/223932792.shtml
> http://www.techdirt.com/articles/20080408/223932792.shtml
> http://www.techdirt.com/
> http://www.techdirt.com/
> http://techdirt.com/index.php
> http://techdirt.com/blog.php?tag=loans
>
>
>
> I'd like to extract
>
> wiseclerk.com
> tiscali.co.uk
> techdirt.com
>
> I'm struggling to find the correct algorithm to locate the starting
> point (after the http:// or after the http://www.) and the ending
> point (the first /) for my Mid function.
>
> Any help appreciated.
>
> I'm thinking that I might have to do a find/search function for either
> of the two starting strings and then have the "max" value (for the
> longer of the two strings) but that's a lot of extra columns in my
> spreadsheet.
>
> Is there perhaps a better way to do this? 

0
4/20/2008 6:07:12 PM
On Apr 20, 1:07 pm, "Nick Hodge" <nick_ho...@zen.co.uk> wrote:
> Mike
>
> Try something like
>
> =IF(ISERR(FIND("www",A1,1)),MID(A1,8,FIND("/",A1,8)-8),MID(A1,12,FIND("/",A1,12)-12))
>
> this works on the theory that there is a trailing / after the domain
> somewhere, it will need amending if there is not, e.ghttp://excelusergroup.org
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHIS...@zen.co.ukANDTHIS
> web:www.excelusergroup.org
> web:www.nickhodge.co.uk
>
> "MikeB" <MPBr...@gmail.com> wrote in message
>
> news:fadd409d-aee3-4b9f-ae9e-4ce3501963aa@24g2000hsh.googlegroups.com...
>
> > Hi, I have a list of URLs and I would like to find a way to extract
> > the domain name from the URL.
>
> > The URLs can be prefixed with www in some cases and I'd also like to
> > strip that off.
>
> > So, for the following URLs
> >http://www.wiseclerk.com/group-news/tag/libor
> >http://www.wiseclerk.com/group-news/2008/04/
> >http://www.wiseclerk.com/group-news/
> >http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
> > sports
> >http://www.techdirt.com/articles/20080408/223932792.shtml
> >http://www.techdirt.com/articles/20080408/223932792.shtml
> >http://www.techdirt.com/
> >http://www.techdirt.com/
> >http://techdirt.com/index.php
> >http://techdirt.com/blog.php?tag=loans
>
> > I'd like to extract
>
> > wiseclerk.com
> > tiscali.co.uk
> > techdirt.com
>
> > I'm struggling to find the correct algorithm to locate the starting
> > point (after the http:// or after thehttp://www.) and the ending
> > point (the first /) for my Mid function.
>
> > Any help appreciated.
>
> > I'm thinking that I might have to do a find/search function for either
> > of the two starting strings and then have the "max" value (for the
> > longer of the two strings) but that's a lot of extra columns in my
> > spreadsheet.
>
> > Is there perhaps a better way to do this?

Hey Nick! Some years ago I had the pleasure of living in Romsey near
Southampton. I just saw your signature and that brought back fond
memories.
0
MPBrede (29)
4/20/2008 6:13:55 PM
That doesn't seem to work for URLs like these...

www.techdirt.com/articles/20080408/223932792.shtml

http://www.com/

Rick



"Nick Hodge" <nick_hodge@zen.co.uk> wrote in message 
news:eteZiFxoIHA.524@TK2MSFTNGP05.phx.gbl...
> Mike
>
> Try something like
>
> =IF(ISERR(FIND("www",A1,1)),MID(A1,8,FIND("/",A1,8)-8),MID(A1,12,FIND("/",A1,12)-12))
>
> this works on the theory that there is a trailing / after the domain 
> somewhere, it will need amending if there is not, e.g 
> http://excelusergroup.org
>
> -- 
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> web: www.excelusergroup.org
> web: www.nickhodge.co.uk
>
>
>
>
>
> "MikeB" <MPBrede@gmail.com> wrote in message 
> news:fadd409d-aee3-4b9f-ae9e-4ce3501963aa@24g2000hsh.googlegroups.com...
>> Hi, I have a list of URLs and I would like to find a way to extract
>> the domain name from the URL.
>>
>> The URLs can be prefixed with www in some cases and I'd also like to
>> strip that off.
>>
>> So, for the following URLs
>> http://www.wiseclerk.com/group-news/tag/libor
>> http://www.wiseclerk.com/group-news/2008/04/
>> http://www.wiseclerk.com/group-news/
>> http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
>> sports
>> http://www.techdirt.com/articles/20080408/223932792.shtml
>> http://www.techdirt.com/articles/20080408/223932792.shtml
>> http://www.techdirt.com/
>> http://www.techdirt.com/
>> http://techdirt.com/index.php
>> http://techdirt.com/blog.php?tag=loans
>>
>>
>>
>> I'd like to extract
>>
>> wiseclerk.com
>> tiscali.co.uk
>> techdirt.com
>>
>> I'm struggling to find the correct algorithm to locate the starting
>> point (after the http:// or after the http://www.) and the ending
>> point (the first /) for my Mid function.
>>
>> Any help appreciated.
>>
>> I'm thinking that I might have to do a find/search function for either
>> of the two starting strings and then have the "max" value (for the
>> longer of the two strings) but that's a lot of extra columns in my
>> spreadsheet.
>>
>> Is there perhaps a better way to do this?
> 

0
4/20/2008 6:17:27 PM
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:eXZRy8woIHA.4308@TK2MSFTNGP06.phx.gbl...
> I think this formula does what you want and handles all the various
> possibilities...
>
>
=IF(LEFT(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUTE(A1,"http://","
")&"/")-1),4)="www.",MID(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUT
E(A1,"http://","")&"/")-1),5,256),LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",
SUBSTITUTE(A1,"http://","")&"/")-1))

That does not work for URLs that start with, say, ftp.

The type of the URL is relevant, e.g., what about ftp.xyz.com and
www.xyz.com, both  should result in same doughmain.

In this case, a worksheet function may be better..


0
kaikow (53)
4/20/2008 7:48:10 PM
Well, I could modify my formula to account for the FTP protocol (and, by 
changing the approach, maybe so others even), but there seems to be so many 
protocols possible... even if you wrote a function to handle them, which 
protocols would you select to handle?

Rick


"Howard Kaikow" <kaikow@standards.com> wrote in message 
news:eAuXb9xoIHA.4848@TK2MSFTNGP05.phx.gbl...
> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:eXZRy8woIHA.4308@TK2MSFTNGP06.phx.gbl...
>> I think this formula does what you want and handles all the various
>> possibilities...
>>
>>
> =IF(LEFT(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUTE(A1,"http://","
> ")&"/")-1),4)="www.",MID(LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",SUBSTITUT
> E(A1,"http://","")&"/")-1),5,256),LEFT(SUBSTITUTE(A1,"http://",""),FIND("/",
> SUBSTITUTE(A1,"http://","")&"/")-1))
>
> That does not work for URLs that start with, say, ftp.
>
> The type of the URL is relevant, e.g., what about ftp.xyz.com and
> www.xyz.com, both  should result in same doughmain.
>
> In this case, a worksheet function may be better..
>
> 

0
4/21/2008 3:49:08 PM
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:OGo08c8oIHA.3860@TK2MSFTNGP02.phx.gbl...
> Well, I could modify my formula to account for the FTP protocol (and, by
> changing the approach, maybe so others even), but there seems to be so
many
> protocols possible... even if you wrote a function to handle them, which
> protocols would you select to handle?

There is no simple answer.
It's up to the programmer.

A function may be easier because there are likely Windows APIs for parsing
URLs.


0
kaikow (53)
4/22/2008 8:50:37 PM
>> Well, I could modify my formula to account for the FTP protocol (and, by
>> changing the approach, maybe so others even), but there seems to be so
> many
>> protocols possible... even if you wrote a function to handle them, which
>> protocols would you select to handle?
>
> There is no simple answer.
> It's up to the programmer.
>
> A function may be easier because there are likely Windows APIs for parsing
> URLs.

Fair enough. Here is a possible Function solution to this question which 
makes use of the UrlGetPart API to get to the "company name". For those 
following this thread, add a Module to the project (Insert/Module from the 
VBA editor) and copy/paste the code after my signature into its code window. 
Inside the GetCompanyName function, I have seeded the Protocols constant 
with "www" and "ftp"... you can add any other protocols you think necessary 
in this constant, just make sure each protocol is separated by a dot. You 
would use this function just like any other worksheet function. So, for 
example, if A1 contains the URL, then put this formula in the cell you want 
to extract the company name to...

=GetCompanyName(A1)

It is that simple (assuming I didn't screw up my code that is<g>).

Rick

Private Declare Function UrlGetPart Lib "shlwapi" _
                Alias "UrlGetPartA" _
               (ByVal pszIn As String, _
                ByVal pszOut As String, _
                pcchOut As Long, _
                ByVal dwPart As Long, _
                ByVal Flags As Long) As Long

Private Const MAX_PATH As Long = 260
Private Const URL_PART_HOSTNAME As Long = 2

Public Function GetCompanyName(URL As String) As String
   Dim dwPart As Long
   Dim Flags As Long
   Dim Part As String
   Dim Size As Long
   Dim Host As String
   '  Protocols is a **dot** delimited string
   Const Protocols As String = "www.ftp"
   If Len(URL) > 0 Then
      If InStr(URL, "//") = 0 Then URL = "http://" & URL
      Part = Space$(MAX_PATH)
      Size = Len(Part)
      If UrlGetPart(URL, Part, Size, URL_PART_HOSTNAME, 0&) = 0 Then
         GetCompanyName = Left$(Part, Size)
      End If
      Host = Left(GetCompanyName, InStr(GetCompanyName, "."))
      If InStr("." & Protocols & ".", "." & Host) Then
        GetCompanyName = Mid(GetCompanyName, Len(Host) + 1)
      End If
   End If
End Function 

0
4/22/2008 9:43:58 PM
On Sun, 20 Apr 2008 10:17:08 -0700 (PDT), MikeB <MPBrede@gmail.com> wrote:

>Hi, I have a list of URLs and I would like to find a way to extract
>the domain name from the URL.
>
>The URLs can be prefixed with www in some cases and I'd also like to
>strip that off.
>
>So, for the following URLs
>http://www.wiseclerk.com/group-news/tag/libor
>http://www.wiseclerk.com/group-news/2008/04/
>http://www.wiseclerk.com/group-news/
>http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
>sports
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/
>http://www.techdirt.com/
>http://techdirt.com/index.php
>http://techdirt.com/blog.php?tag=loans
>
>
>
>I'd like to extract
>
>wiseclerk.com
>tiscali.co.uk
>techdirt.com
>
>I'm struggling to find the correct algorithm to locate the starting
>point (after the http:// or after the http://www.) and the ending
>point (the first /) for my Mid function.
>
>Any help appreciated.
>
>I'm thinking that I might have to do a find/search function for either
>of the two starting strings and then have the "max" value (for the
>longer of the two strings) but that's a lot of extra columns in my
>spreadsheet.
>
>Is there perhaps a better way to do this?


Since you indicated that these are URL's, I will assume they have a normal
structure, so the domain name will be the part between the first // and the
next /.  Then we just remove the www. if it exists.


=SUBSTITUTE(MID(A1,FIND("/",A1)+2,FIND(CHAR(1),
SUBSTITUTE(A1,"/",CHAR(1),3))-FIND("/",A1)-2),"www.","")

--ron
0
ronrosenfeld (3122)
4/23/2008 2:08:18 AM
On Sun, 20 Apr 2008 10:17:08 -0700 (PDT), MikeB <MPBrede@gmail.com> wrote:

>Hi, I have a list of URLs and I would like to find a way to extract
>the domain name from the URL.
>
>The URLs can be prefixed with www in some cases and I'd also like to
>strip that off.
>
>So, for the following URLs
>http://www.wiseclerk.com/group-news/tag/libor
>http://www.wiseclerk.com/group-news/2008/04/
>http://www.wiseclerk.com/group-news/
>http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
>sports
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/
>http://www.techdirt.com/
>http://techdirt.com/index.php
>http://techdirt.com/blog.php?tag=loans
>
>
>
>I'd like to extract
>
>wiseclerk.com
>tiscali.co.uk
>techdirt.com
>
>I'm struggling to find the correct algorithm to locate the starting
>point (after the http:// or after the http://www.) and the ending
>point (the first /) for my Mid function.
>
>Any help appreciated.
>
>I'm thinking that I might have to do a find/search function for either
>of the two starting strings and then have the "max" value (for the
>longer of the two strings) but that's a lot of extra columns in my
>spreadsheet.
>
>Is there perhaps a better way to do this?

Missed something -- the case where there is no final "/".

So use this instead:

=SUBSTITUTE(MID(A1,FIND("/",A1)+2,IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))=2,
255,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))-FIND("/",A1)-2)),"www.","")

--ron
0
ronrosenfeld (3122)
4/23/2008 2:15:14 AM
"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:ufRb2HMpIHA.4308@TK2MSFTNGP06.phx.gbl...
> Fair enough. Here is a possible Function solution to this question which
> makes use of the UrlGetPart API to get to the "company name". For those
> following this thread, add a Module to the project (Insert/Module from the
> VBA editor) and copy/paste the code after my signature into its code
window.
> Inside the GetCompanyName function, I have seeded the Protocols constant
> with "www" and "ftp"... you can add any other protocols you think
necessary
> in this constant, just make sure each protocol is separated by a dot. You
> would use this function just like any other worksheet function. So, for
> example, if A1 contains the URL, then put this formula in the cell you
want
> to extract the company name to...

It is almost always better to use the Unicode version of APIs.


0
kaikow (53)
4/23/2008 6:25:51 AM
>> Fair enough. Here is a possible Function solution to this question which
>> makes use of the UrlGetPart API to get to the "company name". For those
>> following this thread, add a Module to the project (Insert/Module from 
>> the
>> VBA editor) and copy/paste the code after my signature into its code
> window.
>> Inside the GetCompanyName function, I have seeded the Protocols constant
>> with "www" and "ftp"... you can add any other protocols you think
> necessary
>> in this constant, just make sure each protocol is separated by a dot. You
>> would use this function just like any other worksheet function. So, for
>> example, if A1 contains the URL, then put this formula in the cell you
> want
>> to extract the company name to...
>
> It is almost always better to use the Unicode version of APIs.

You are probably right; but I have zero experience with international 
programming issues (all my programming efforts for the past 27 years have 
been US based), whether involving fonts or not. The end result is I have no 
idea if there is anything special  that needs to be accounted for or not 
when dealing with them. My gut feeling is simply using UrlGetPartW in place 
of UrlGetPart is not the whole answer.

Rick 

0
4/23/2008 8:24:09 AM
I really tied up now, but the following  will give you an idea on how  to
Convert to Unicode.
Apparently, the code is still not parsing as desired.

Option Explicit

Private Declare Function UrlGetPart Lib "shlwapi" _
                Alias "UrlGetPartW" _
               (ByVal pszIn As Long, _
                ByVal pszOut As Long, _
                pcchOut As Long, _
                ByVal dwPart As Long, _
                ByVal Flags As Long) As Long

Private Const MAX_PATH As Long = 260
Private Const URL_PART_HOSTNAME As Long = 2

Public Function GetCompanyName(URL As String) As String
   Dim dwPart As Long
   Dim Flags As Long
   Dim Part As String
   Dim Size As Long
   Dim Host As String
   '  Protocols is a **dot** delimited string
   Const Protocols As String = "www.ftp"
   If Len(URL) > 0 Then
      If InStr(URL, "//") = 0 Then URL = "http://" & URL
      Part = Space$(MAX_PATH)
      Size = Len(Part)
      If UrlGetPart(StrPtr(URL), StrPtr(Part), Size, URL_PART_HOSTNAME, 0&)
= 0 Then
         GetCompanyName = Left$(Part, Size)
      End If
      Host = Left(GetCompanyName, InStr(GetCompanyName, "."))
      If InStr("." & Protocols & ".", "." & Host) Then
        GetCompanyName = Mid(GetCompanyName, Len(Host) + 1)
      End If
   End If
End Function

Private Sub btnByeBye_Click()
    Unload Me
End Sub

Private Sub btnRunMe_Click()
    With lstResult
        .AddItem GetCompanyName("http://www.BagelsAndLox.com/")
        .AddItem GetCompanyName("http://BagelsAndLox.com/")
        .AddItem GetCompanyName("www.BagelsAndLox.com")
        .AddItem GetCompanyName("BagelsAndLox.com")
        .AddItem GetCompanyName("http://www.Bob.BagelsAndLox.com/")
        .AddItem GetCompanyName("http://Carol.BagelsAndLox.com/")
        .AddItem GetCompanyName("www.Ted.BagelsAndLox.com")
        .AddItem GetCompanyName("Alice.BagelsAndLox.com")
    End With
End Sub


0
kaikow (53)
4/23/2008 9:17:00 AM
A betta test is:


Private Sub btnRunMe_Click()
    With lstResult
        .AddItem GetCompanyName("http://www.BagelsAndLox.com/")
        .AddItem GetCompanyName("http://BagelsAndLox.com/")
        .AddItem GetCompanyName("www.BagelsAndLox.com")
        .AddItem GetCompanyName("BagelsAndLox.com")
        .AddItem GetCompanyName("http://www.Bob.BagelsAndLox.com/")
        .AddItem GetCompanyName("http://Carol.BagelsAndLox.com/")
        .AddItem GetCompanyName("www.Ted.BagelsAndLox.com")
        .AddItem GetCompanyName("Alice.BagelsAndLox.com")

        .AddItem GetCompanyName("http://www.BagelsAndLox.com/Alpha.htm")
        .AddItem GetCompanyName("http://BagelsAndLox.com/Beta.html")
        .AddItem GetCompanyName("http://www.Bob.BagelsAndLox.com/Gamma.doc")
        .AddItem GetCompanyName("http://Carol.BagelsAndLox.com/Delta.jpg")
    End With
End Sub




0
kaikow (53)
4/23/2008 9:26:26 AM
On Sun, 20 Apr 2008 10:17:08 -0700 (PDT), MikeB <MPBrede@gmail.com> wrote:

>Hi, I have a list of URLs and I would like to find a way to extract
>the domain name from the URL.
>
>The URLs can be prefixed with www in some cases and I'd also like to
>strip that off.
>
>So, for the following URLs
>http://www.wiseclerk.com/group-news/tag/libor
>http://www.wiseclerk.com/group-news/2008/04/
>http://www.wiseclerk.com/group-news/
>http://www.tiscali.co.uk/search/results.php?section=&from=&query=setana
>sports
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/articles/20080408/223932792.shtml
>http://www.techdirt.com/
>http://www.techdirt.com/
>http://techdirt.com/index.php
>http://techdirt.com/blog.php?tag=loans
>
>
>
>I'd like to extract
>
>wiseclerk.com
>tiscali.co.uk
>techdirt.com
>
>I'm struggling to find the correct algorithm to locate the starting
>point (after the http:// or after the http://www.) and the ending
>point (the first /) for my Mid function.
>
>Any help appreciated.
>
>I'm thinking that I might have to do a find/search function for either
>of the two starting strings and then have the "max" value (for the
>longer of the two strings) but that's a lot of extra columns in my
>spreadsheet.
>
>Is there perhaps a better way to do this?

Here is a UDF that will probably do a better job, given all the variables that
have been proposed in this thread.

Note that the line that starts with  re.Pattern = and the subsequent line(s)
within quotation marks should be all on one line.

======================================
Option Explicit
Function ExtrURL(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern =
"\b((https?|ftp)://)?([\-A-Z0-9.]+)(/[\-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[\-A-Z0-9+&@#/%=~_|!:,.;]*)?"
If re.test(str) = True Then
    Set mc = re.Execute(str)
    ExtrURL = mc(mc.Count - 1).submatches(2)
    ExtrURL = Replace(ExtrURL, "www.", "")
    If InStr(1, ExtrURL, ".") = 0 Then ExtrURL = ""
End If
End Function
==================================

--ron
0
ronrosenfeld (3122)
4/23/2008 1:38:59 PM
> re.Pattern =
> "\b((https?|ftp)://)?([\-A-Z0-9.]+)(/[\-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[\-A-Z0-9+&@#/%=~_|!:,.;]*)?"

Now that is what I miss about Regular Expressions from my days many years 
ago working with them in the UNIX world... their clarity and readability.<g>

Rick 

0
4/23/2008 2:23:45 PM
On Wed, 23 Apr 2008 10:23:45 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>> re.Pattern =
>> "\b((https?|ftp)://)?([\-A-Z0-9.]+)(/[\-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[\-A-Z0-9+&@#/%=~_|!:,.;]*)?"
>
>Now that is what I miss about Regular Expressions from my days many years 
>ago working with them in the UNIX world... their clarity and readability.<g>
>
>Rick 

<ggg>

And even when you write out the explanation:

===============================
URL capturing

\b((https?|ftp)://)?([-A-Z0-9.]+)(/[-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[-A-Z0-9+&@#/%=~_|!:,.;]*)?

Options: case insensitive

Assert position at a word boundary �\b�
Match the regular expression below and capture its match into backreference
number 1 �((https?|ftp)://)?�
   Between zero and one times, as many times as possible, giving back as needed
(greedy) �?�
   Match the regular expression below and capture its match into backreference
number 2 �(https?|ftp)�
      Match either the regular expression below (attempting the next
alternative only if this one fails) �https?�
         Match the characters �http� literally �http�
         Match the character �s� literally �s?�
            Between zero and one times, as many times as possible, giving back
as needed (greedy) �?�
      Or match regular expression number 2 below (the entire group fails if
this one fails to match) �ftp�
         Match the characters �ftp� literally �ftp�
   Match the characters �://� literally �://�
Match the regular expression below and capture its match into backreference
number 3 �([-A-Z0-9.]+)�
   Match a single character present in the list below �[-A-Z0-9.]+�
      Between one and unlimited times, as many times as possible, giving back
as needed (greedy) �+�
      The character �-� �-�
      A character in the range between �A� and �Z� �A-Z�
      A character in the range between �0� and �9� �0-9�
      The character �.� �.�
Match the regular expression below and capture its match into backreference
number 4 �(/[-A-Z0-9+&@#/%=~_|!:,.;]*)?�
   Between zero and one times, as many times as possible, giving back as needed
(greedy) �?�
   Match the character �/� literally �/�
   Match a single character present in the list below
�[-A-Z0-9+&@#/%=~_|!:,.;]*�
      Between zero and unlimited times, as many times as possible, giving back
as needed (greedy) �*�
      The character �-� �-�
      A character in the range between �A� and �Z� �A-Z�
      A character in the range between �0� and �9� �0-9�
      One of the characters �+&@#/%=~_|!:,.;� �+&@#/%=~_|!:,.;�
Match the regular expression below and capture its match into backreference
number 5 �(\?[-A-Z0-9+&@#/%=~_|!:,.;]*)?�
   Between zero and one times, as many times as possible, giving back as needed
(greedy) �?�
   Match the character �?� literally �\?�
   Match a single character present in the list below
�[-A-Z0-9+&@#/%=~_|!:,.;]*�
      Between zero and unlimited times, as many times as possible, giving back
as needed (greedy) �*�
      The character �-� �-�
      A character in the range between �A� and �Z� �A-Z�
      A character in the range between �0� and �9� �0-9�
      One of the characters �+&@#/%=~_|!:,.;� �+&@#/%=~_|!:,.;�


Created with RegexBuddy
======================================
--ron
0
ronrosenfeld (3122)
4/23/2008 6:33:31 PM
Did this issue  ever get solved?


0
kaikow (53)
4/26/2008 12:11:52 PM
Ron Rosenfeld <ronrosenf...@nospam.org> wrote...
....
[reformatted]
>re.Pattern = "\b((https?|ftp)://)?([\-A-Z0-9.]+)" & _
> "(/[\-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[\-A-Z0-9+&@#/%=~_|!:,.;]*)?"
....

Why so verbose?

re.Pattern = "[^:]*:(//)?[^/:]*?([^./:]+\.[^./:]+(\.[a-z]{2})?)[:/].*"
ExtrURL = re.Replace(str, "$2")
0
hrlngrv1 (375)
4/27/2008 3:27:28 AM
On Sat, 26 Apr 2008 20:27:28 -0700 (PDT), Harlan Grove <hrlngrv@gmail.com>
wrote:

>Ron Rosenfeld <ronrosenf...@nospam.org> wrote...
>...
>[reformatted]
>>re.Pattern = "\b((https?|ftp)://)?([\-A-Z0-9.]+)" & _
>> "(/[\-A-Z0-9+&@#/%=~_|!:,.;]*)?(\?[\-A-Z0-9+&@#/%=~_|!:,.;]*)?"
>...
>
>Why so verbose?
>
>re.Pattern = "[^:]*:(//)?[^/:]*?([^./:]+\.[^./:]+(\.[a-z]{2})?)[:/].*"
>ExtrURL = re.Replace(str, "$2")

It's a pattern (from a library) that captures the different URL parts into
different backreferences, so does more than what the OP requested.

But I did test against all the content mentioned in the thread.

Running a quick test, using
============================
Function Extr(str As String) As String
Dim re As Object
    Set re = CreateObject("vbscript.regexp")
    	re.IgnoreCase = True
        re.Global = True
        re.Pattern = "[^:]*:(//)?[^/:]*?([^./:]+\.[^./:]+(\.[a-z]{2})?)[:/].*"
    If re.Test(str) = True Then
        Extr = re.Replace(str, "$2")
    End If
End Function
================================


Your pattern doesn't seem to match:

http://excelusergroup.org
www.techdirt.com/articles/20080408/223932792.shtml

www.BagelsAndLox.com
BagelsAndLox.com
www.Ted.BagelsAndLox.com
Alice.BagelsAndLox.com

and won't extract the URL from
	.AddItem GetCompanyName("www.BagelsAndLox.com")
        .AddItem GetCompanyName("BagelsAndLox.com")
	.AddItem GetCompanyName("www.Ted.BagelsAndLox.com")
        .AddItem GetCompanyName("Alice.BagelsAndLox.com")


Granted, these kinds of examples were not all in the OP's specifications.
--ron
0
ronrosenfeld (3122)
4/27/2008 11:46:30 AM
Ron Rosenfeld <ronrosenf...@nospam.org> wrote...
....
>But I did test against all the content mentioned in the thread.
....
>Your pattern doesn't seem to match:
>
>http://excelusergroup.orgwww.techdirt.com/articles/20080408/223932792.shtml

The domain name in the url above should be

techdirt.com

and that's what my approach returns.

>www.BagelsAndLox.com
>BagelsAndLox.comwww.Ted.BagelsAndLox.com
>Alice.BagelsAndLox.com
....

I don't consider these urls. They're missing a protocol specifier
(http, https, ftp, or mailto, news, gofer, etc.) All depends on how we
define urls, but there could be substrings in arbitrary text that
match \b[^. ]+\.[^. ]\b that aren't urls, e.g., section numbers like
2.34.5. How would one distinguish these from urls without making the
protocol specifiers mandatory?
0
hrlngrv1 (375)
4/28/2008 9:35:06 PM
On Mon, 28 Apr 2008 14:35:06 -0700 (PDT), Harlan Grove <hrlngrv@gmail.com>
wrote:

>I don't consider these urls. They're missing a protocol specifier
>(http, https, ftp, or mailto, news, gofer, etc.) All depends on how we
>define urls, but there could be substrings in arbitrary text that
>match \b[^. ]+\.[^. ]\b that aren't urls, e.g., section numbers like
>2.34.5. How would one distinguish these from urls without making the
>protocol specifiers mandatory?

A valid objection.

Of course, we could just go back to the OP's original request:

>I'm struggling to find the correct algorithm to locate the starting
>point (after the http:// or after the http://www.) and the ending
>point (the first /) for my Mid function.

which can be easily handled with a worksheet function, and given his
description of "having a list of URL's" are probably not embedded in text.

--ron
0
ronrosenfeld (3122)
4/29/2008 2:27:38 AM
Ron Rosenfeld <ronrosenf...@nospam.org> wrote...
....
>Of course, we could just go back to the OP's original request:
>
>>I'm struggling to find the correct algorithm to locate the starting
>>point (after the http:// or after thehttp://www.) and the ending
>>point (the first /) for my Mid function.
>
>which can be easily handled with a worksheet function, and given his
>description of "having a list of URL's" are probably not embedded in
>text.

OP's often don't provide comprehensive examples, as you know. If the
urls always have protocol specifiers, and there's always 2 slashes
just after the protocol specifier and colon, then the domain name will
appear between :// and the subsequent /, but such urls *can* also
contain port number specifiers. For example,

http://www.foo.com:80/bar/

which your approach chokes on but mine parses as foo.com. Then there
are mailto: and news: protocol specifiers that aren't followed by two
slashes, but they're perhaps a digression.

The domain name will be the last 2 or 3 period-separated tokens
between the first colon, possibly followed by 2 slashes, and the first
subsequent colon or slash. The only characters you need to check for
as delimiters are colons and slashes. The domain name will contain 1
or 2 periods separating any other characters.
0
hrlngrv1 (375)
4/29/2008 8:00:55 PM
The inclusion of the whatever:// is irrelevant to the issue of extracting
the domain.
Proper code will work either way.
And do not forget about country codes at the end of the string/URL.


0
kaikow (53)
4/30/2008 4:19:38 AM
"Howard Kaikow" <kai...@standards.com> wrote...
>The inclusion of the whatever:// is irrelevant to the issue of extracting
>the domain.
>Proper code will work either way.
>And do not forget about country codes at the end of the string/URL.

Really? What code would handle all the following?

http://linuxtoday.com/
http://www.firstmonday.dk/issues/issue3_3/raymond/
http://www.ace.net.nz/tech/TechFileFormat.html#s
http://www.ifi.unizh.ch/richter/people/pilz/links/index.html
http://www.insurance.ca.gov/docs/index.html
http://www.tdi.state.tx.us/wc/indexwc.html
http://xcell05.free.fr/pages/prog/api-c.htm
http://www.science.uva.nl/research/air/wiki/ShellStartupFiles
http://en-US.www.mozilla.com/en-US/firefox/help/
http://xxx.lanl.gov/
http://www.stats.ox.ac.uk/pub/MASS4/
http://caml.inria.fr/
http://www.er.uqam.ca/nobel/r10735/linux.html
http://gd.tuwien.ac.at/opsys/linux/RPM/
http://perso.wanadoo.es/antlarr/kalamaris.html

where the domain names should be

linuxtoday.com
firstmonday.dk
ace.net.nz
unizh.ch
ca.gov
state.tx.us
free.fr
uva.nl
mozilla.com
lanl.gov
ox.ac.uk
inria.fr
uqam.ca
tuwien.ac.at
wanadoo.es

It seems country top-level domains (.uk, .ca, .es, .dk, .fr, etc)
don't have to have US-like top-level domains
(.com, .net, .org, .gov, .edu, etc), but they can have optional
alternatives (.ac for .edu, .co for .com). But the presence of .??.us
where the ?? are 2-char abbreviations for US states or territories
really screws up simple rules.
0
hrlngrv1 (375)
5/1/2008 2:02:08 AM
On Tue, 29 Apr 2008 13:00:55 -0700 (PDT), Harlan Grove <hrlngrv@gmail.com>
wrote:

>Ron Rosenfeld <ronrosenf...@nospam.org> wrote...
>...
>>Of course, we could just go back to the OP's original request:
>>
>>>I'm struggling to find the correct algorithm to locate the starting
>>>point (after the http:// or after thehttp://www.) and the ending
>>>point (the first /) for my Mid function.
>>
>>which can be easily handled with a worksheet function, and given his
>>description of "having a list of URL's" are probably not embedded in
>>text.
>
>OP's often don't provide comprehensive examples, as you know. If the
>urls always have protocol specifiers, and there's always 2 slashes
>just after the protocol specifier and colon, then the domain name will
>appear between :// and the subsequent /, but such urls *can* also
>contain port number specifiers. For example,
>
>http://www.foo.com:80/bar/
>
>which your approach chokes on but mine parses as foo.com. Then there
>are mailto: and news: protocol specifiers that aren't followed by two
>slashes, but they're perhaps a digression.
>
>The domain name will be the last 2 or 3 period-separated tokens
>between the first colon, possibly followed by 2 slashes, and the first
>subsequent colon or slash. The only characters you need to check for
>as delimiters are colons and slashes. The domain name will contain 1
>or 2 periods separating any other characters.

Actually, my VBA regex approaches parses out port specifiers OK.  But I think
there is confusion, for me and others, about what constitutes a "domain name".
(I'm not particularly knowledgeable here).  

But I see definitions for URL; domain name; registered domain name; hostname;
as well as various types of Top Level Domains (generic, country specific);
second level domains; and various levels of subdomains.

And the specifications are changing.  Including allowing the use non-ascii
characters both in country level TLD's as well as in legitimate domain names.

In any event, the OP said he had a list of URL's; wanted to extract the domain
name; and remove the www. if present.

So I have simplified my original regex and VBA routine to do that.  I start
matching at the first ":", with an optional "//"; capture the (www.) into a
group which I will ignore, and return the subsequent string that includes
letters, digits, underscore, hyphens and dots.

re.Pattern = ":(//)?(www\.)?([-\w.]+)"

This returns the domains and all the subdomains, with the exception of the
"www."

There are some differences in what we return in some of the URL's you listed.
I'm not sure what the OP would want.  For some of them, he might want the
leftmost subdomain, and for others not.

URL
http://www.firstmonday.dk/issues/issue3_3/raymond/
http://www.insurance.ca.gov/docs/index.html
http://www.tdi.state.tx.us/wc/indexwc.html
http://en-US.www.mozilla.com/en-US/firefox/help/
http://xxx.lanl.gov/
http://www.stats.ox.ac.uk/pub/MASS4/
http://gd.tuwien.ac.at/opsys/linux/RPM/


Ron			Harlan
firstmonday.dk		www.firstmonday.dk
insurance.ca.gov	ca.gov
tdi.state.tx.us		state.tx.us
en-US.www.mozilla.com	mozilla.com
xxx.lanl.gov		lanl.gov
stats.ox.ac.uk		ox.ac.uk
gd.tuwien.ac.at		tuwien.ac.at


I can "correct" the entry with mozilla.com by making a small change in my
regex:

":(//)?([-\w.]*www\.)?([-\w.]+)"

and that works on the samples you provided.  But I don't know if it would work
in all cases.

In addition, as you know, javascript does not match unicode characters, so that
causes another set of problems :-(

Enought for now -- I've got some errands to do.  Below is the VBA code I used:

Ron:
====================================
Function ExtrURL(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = False
're.Pattern = ":(//)?(www\.)?([-\w.]+)"
re.Pattern = ":(//)?([-\w.]*www\.)?([-\w.]+)"
If re.test(str) = True Then
    Set mc = re.Execute(str)
    ExtrURL = mc(mc.Count - 1).submatches(2)
End If
End Function

'Harlan--------------------------------------------------------

Function ExtrURLH(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "[^:]*:(//)?[^/:]*?([^./:]+\.[^./:]+(\.[a-z]{2})?)[:/].*"
ExtrURLH = re.Replace(str, "$2")
End Function
=======================================

Best,
--ron
0
ronrosenfeld (3122)
5/1/2008 12:53:40 PM
First you will need to answer how a human can tell what the domain part is for
these examples. The only way I could think of would be to query "Who Is" and
look at the registrant data. 

The problem is com, nz, uk etc. are all domains
so are net.nz and ace.net.nz. ace.net.nz is a sub domain of net.nz but then net
is a sub domain of nz. Since the domain itself can have an IP and be used to
point to a web server there is no way you can extract what you have defined as
the domain part from the string programmatically. The solution needs additional
data about what you consider is the boundary point in each string.


On Wed, 30 Apr 2008 19:02:08 -0700 (PDT), Harlan Grove <hrlngrv@gmail.com>
wrote:

>"Howard Kaikow" <kai...@standards.com> wrote...
>>The inclusion of the whatever:// is irrelevant to the issue of extracting
>>the domain.
>>Proper code will work either way.
>>And do not forget about country codes at the end of the string/URL.
>
>Really? What code would handle all the following?
>
>http://linuxtoday.com/
>http://www.firstmonday.dk/issues/issue3_3/raymond/
>http://www.ace.net.nz/tech/TechFileFormat.html#s
>http://www.ifi.unizh.ch/richter/people/pilz/links/index.html
>http://www.insurance.ca.gov/docs/index.html
>http://www.tdi.state.tx.us/wc/indexwc.html
>http://xcell05.free.fr/pages/prog/api-c.htm
>http://www.science.uva.nl/research/air/wiki/ShellStartupFiles
>http://en-US.www.mozilla.com/en-US/firefox/help/
>http://xxx.lanl.gov/
>http://www.stats.ox.ac.uk/pub/MASS4/
>http://caml.inria.fr/
>http://www.er.uqam.ca/nobel/r10735/linux.html
>http://gd.tuwien.ac.at/opsys/linux/RPM/
>http://perso.wanadoo.es/antlarr/kalamaris.html
>
>where the domain names should be
>
>linuxtoday.com
>firstmonday.dk
>ace.net.nz
>unizh.ch
>ca.gov
>state.tx.us
>free.fr
>uva.nl
>mozilla.com
>lanl.gov
>ox.ac.uk
>inria.fr
>uqam.ca
>tuwien.ac.at
>wanadoo.es
>
>It seems country top-level domains (.uk, .ca, .es, .dk, .fr, etc)
>don't have to have US-like top-level domains
>(.com, .net, .org, .gov, .edu, etc), but they can have optional
>alternatives (.ac for .edu, .co for .com). But the presence of .??.us
>where the ?? are 2-char abbreviations for US states or territories
>really screws up simple rules.
-- 
Dave Mills
There are 10 type of people, those that understand binary and those that don't.
0
News11 (66)
5/1/2008 4:25:59 PM
Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote...
>First you will need to answer how a human can tell what the domain
>part is for these examples. . . .
....
>The problem is com, nz, uk etc. are all domains
>so are net.nz and ace.net.nz. ace.net.nz is a sub domain of net.nz
>but then net is a sub domain of nz. Since the domain itself can have
>an IP and be used to point to a web server there is no way you can
>extract what you have defined as the domain part from the string
>programmatically. The solution needs additional data about what you
>consider is the boundary point in each string.
....

There are some rules. Maybe not complete, but they'll cover most
situations. Domains should be parsed right to left by token, and
tokens are period-delimited strings.

If the rightmost token is 2 chars,
  it's a country top-level domain, and presumably more tokens wanted.
  If the next token going left is also 2 chars or a common generic
  top-level domain name (net, org, etc.), then it's presumably also
  a higher level domain. Otherwise, the 2nd token from the right
  would complete the domain name.
  If the rightmost token is us, the next is 2 chars and the next is
  k12, we'd need the 4th token from the right too; otherwise, the 3rd
  token from the right would complete the domain name. Any further
  tokens going left would be hostnames within domain.
Else (the rightmost token is 3 or more chars) the 2nd token from the
  right would complete the domain name.

These rules would fail if www.foobar.museum.ru were a valid url, in
which case the domain name should be foobar.museum.ru. Perhaps what's
needed is a complete list of accepted top-level domain names, then the
domain name would stop at the first token going right to left that
isn't an accepted top-level domain name.

The joker in the set of urls I posted before was stats.ox.ac.uk.
0
hrlngrv1 (375)
5/1/2008 6:59:57 PM
Reply:

Similar Artilces:

Err.Description as error proc name trail, error handling
Hi All, I'm looking for guidance on how to retrofit into a large AddIn the 'best' or possible uses of the Err object in providing an information path to help track down run time errors in a 'production' environment. Some Background, 4 questions follow. I was new to VBA, self taught, when the coding started, (but with experience in other languages), and the vba error handling seemed too complex at the time, huge mistake on my part. The procs below are learning vehicles, which I'm just starting to play with, but they do show the essence o...

Exchange 2003
So I got a reply from a client showing us the header of an email our exchange server sent out. It showed the server name as "server.my.domain" which is internal only and does not resolve on DNS. I thought I had it setup that when emails were sent it would show as "server.fabrikam.com". Obviously I have missed something. Any advice? An additional note to this. In the header the Received: shows the correct DNS information (server.fabrikam.com). But in the Message-ID: it shows <jsdfhuiwefla38rjnfakl@server.my.domain> and not (server.fabrikam.com). ...

Message in queue for local domain
I have three exchange servers, one un used. The Primary is 2003 SP2 and second 2000 latest patch's. Both are Global Catalog Servers. I walk in, in the morning to see 2 messages stuck in queue, on 2000 at the other site (connected via site-to-site T-1). This message was sent at 4:53PM the previous day. It was in "retry" status. I had Diag. Logging enabled on the server. "MSexchangeTransport 4000" - "Message delivery to the remote domain 'julie.esmithgroup.com' failed for the following reason: The connection was dropped by the remote host.&quo...

Outlook 2003 Email Select Names Type name or select from list
New Message: When I try to use the Select Names - Type name or select from list in an Email New Message, the search is not as expected. For example, I enter the Name Smith, and the selection will be another name starting with S, but not Smith. This Address was from Outlook Express and worked fine there. Thank you. Please describe more completely what you are doing. Perhaps you are using autocompletion instead of autoresolution. You didn't provide enough details for us to tell. -- Russ Valentine [MVP-Outlook] "nsnews.microsoft.com" <nfn02958@naples.net> wrote in message...

how to get remote host name
Hello! I have a problem with getting the hostname of the other part of the connection. I had used gethostbyaddr() to retreive the hostname of the other side. (When I am the server.) If the server and the client in the same subnetwork everything work just fine. But as the client come from an other subnetwork this function fails and get back a HOSTENT* structure with NULL value and with and error: this host exists but no associated name. My question is about how to obtain in this case the hostname of the client? There is a general way? Some function? Thanks in advance. Best Regards, Nagyja...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Querying Messages Addressed to a Domain
Dumb Question: We have a single Exchange 2003 server receiving e-mail messages addressed to domain1.com, domain2.com, and domain3.com. We'd like to turn off domain1.com, but want to make sure messages aren't being addressed to that domain even though it's not the primary in the recipient policy. How do I search the information store for any inbound messages addressed to this domain? Can I do it on the fly over the past say 6 months or does some type of logging need to have been enabled? Thanks, Message Tracking would need to be enabled, or SMTP Protocol logging (the la...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Change of domain..
Hi! We´re about to change domain name from .se to .com. I have the following question: Is it possible to generate an auto-reply on the .se address (on user-/mailbox-level) to inform the sender that we have changed address to ..com when the receiver has both addresses( name@domain.se and name@domain.com ) in the exchange mailbox? FYI: It’s not possible to make a general change on all mailboxes because the owner of the .se address still should receive mail. -- ________________________ Per-Olof Olsson IT - Manager I don't know whether that is possible - anyone?? - but you coul...

How to block outgoing e-mail for specified domain in exchange 5.5
Anyone know how to block outgoing e-mail for specified domain in exchange 5.5 e.g. we want to block all outgoing e-mail that send to abc.com. thanks EL In IMC props\Connections tab\Specify by e-mail domain assign abc.com forwarded to 172.16.10.13. EL wrote: > Anyone know how to block outgoing e-mail for specified domain in exchange > 5.5 > e.g. we want to block all outgoing e-mail that send to abc.com. > > thanks > > EL ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Error binding display name
Hello, I have just followed KB article 317327 on my SBS 2003 machine so as to add an email disclaimer. I got the below error when trying to register the sink: - C:\PROGRA~1\Exchsrvr>cd scripts C:\PROGRA~1\Exchsrvr\scripts>cscript smtpreg.vbs /add 1 OnArrival SMTPDisclaimer SMTPEventSink.Disclaimer "mail from=*@mydomain.com" Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. Binding Display Name Specified: SMTPDisclaimer ** Registration Failed ** Err.Number (HRESULT) = 0x1AD Err.Description = Act...

Problems with 'Names'
I am using MS Excel 2007. I have a series of linked files - 1 main 'parent' file with 5 'children' files. The parent file contains 'names' which are used in all the children files (eg..titles across columns, drop down boxes/lists (referencing name ranges) etc) I need to find and replace some of the external links in the files with new file extensions - XLSM and XLSX. I can find and replace as required in all the formulas which work, however the 'names' in the file still refer to the .XLSM file. If you search the entire workbook .XLSM cann...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

info@domain.com on exchange2003
When you have a info@domain.com on a company website is there a way t add this to an smtp address for a specific user already on the networ that is designated to read these emails from the web or do I jus create an address for info -- rick ----------------------------------------------------------------------- rickf's Profile: http://www.msusenet.com/member.php?userid=187 View this thread: http://www.msusenet.com/t-187050703 Create an SMTP address for info@domain.com in the Exhange properties for whoever you want this alias assigned to. "rickf" <rickf.1q0l94@no-mx.msuse...

Sending mail on different connectors by domain
Hi, i'm using Exchange 2003 Enterprise Ed. on cluster configuration and my server manage some domain names, in this moment i have one only SMTP connector pointing to smarthost in dmz. I want to configure some of users in a domain to sending mail directly without passing from the smarthost. (ex: user@xyz.com -> smarthost - user@abc.com -> directly). How can i configure all in order to make that? Someone can help me? Thanks! SMTP connectors can only handle actual domains, not specific e-mail addresses. In this case, you could have your default connector with the address sp...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Creating a macro to name several tabs
I have this macro: Sub Macro() Sheets(1).Name = Range("b1").Value Range("B17").Select End Sub and it works in renaming the tab to whatever is in cell "B1" but I have 30+ tabs that I need to run this for and I can't figure out the rest of the macro needed for this to go thru all of the tabs and not just the first one. Any help would be appreciated! Thanks Try: Sub macro() Dim i As Integer On Error Resume Next For i = 1 To Sheets.Count Sheets(i).Name = Sheets(i).Range("B1").Value Next i On Error GoTo ...

Production Data to Test Environment in same Domain
I have two different OUs set up, one for my Test environment and one for my Production environment. I need to take the data from my production database server and move it to my test database server. Is it as simple as backing up the db files and log files and then restoring them to the test environment or do I have to do a full uninstall along with redeployment on the Test environment? Please advise and thank you much! If the OU is different, you might have to redeploy it. If the domain is the same and the OU is the same, you can restore the database and it will work. Hope this helps...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

Must Enter domain name on OWA 2003 Front End?
I noticed two things happen if you do *not* use your domain name when you log onto the OWA 2003 Front End server: 1. When you use the Logout button you get a prompt for your username and password again 2. When you try to open a link within an email message you are prompted for your username and password, and then you must use your domain\username or else the link will never open. Q. Is this because our Internet Mail domain name and our AD domain name are different? I thought the instructions from Microsoft said you could set a default domain, but the options were only for my email domain...