Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Extract web address

  1. #1
    Guest

    Default

    I have a list of web sites in excel, copied from another program, they are links because you can click on them and go to the web page, they look like this Ask Mr. Excel - Tips and Solutions for Excel
    , is there anyway to extract the web address from it, in this case http://www.mrexcel.com/index.html

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi--

    I have tried to extract this data dfrom ***.eml files and fell flat on my face as outlook extraction into Excel... However

    I have had the nasty task of this:

    I copied and paste then text to cloumns, via wizard and then filtered to http:

    Bingo worked for me |||||

    HTH Some thoughts

    Rdgs
    =========
    Jack


  3. #3
    Guest

    Default

    How do you do this? via wizard and then filtered to http:

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this user-defined function. You can paste it into a module in your workbook. If you don't know how to do this, let me know.
    After you have pasted it into a module in your workbook, just call it like this:

    =ShowAddress(A1)

    Where A1 is the cell with the MR. EXCEL hyperlink in it.


    Option Explicit


    Public Function ShowAddress(rng As Range) As String
    If rng.Cells.Count > 1 Then
    ShowAddress = CVErr(xlErrValue)
    Else
    ShowAddress = rng.Hyperlinks.Item(1).Address
    End If
    End Function



    Hope this helps,

    Russell

  5. #5
    Guest

    Default

    Thanks Russell that works Great!

  6. #6
    New Member
    Join Date
    Jan 2007
    Location
    Jacksonville, FL
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this user-defined function. You can paste it into a module in your workbook. If you don't know how to do this, let me know.
    After you have pasted it into a module in your workbook, just call it like this:

    =ShowAddress(A1)

    Where A1 is the cell with the MR. EXCEL hyperlink in it.


    Option Explicit


    Public Function ShowAddress(rng As Range) As String
    If rng.Cells.Count > 1 Then
    ShowAddress = CVErr(xlErrValue)
    Else
    ShowAddress = rng.Hyperlinks.Item(1).Address
    End If
    End Function



    Hope this helps,

    Russell

    This was great. Thanks***BFL :-(

  7. #7
    New Member
    Join Date
    Jun 2008
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract web address

    Thanks a lot! It's simple to install and works well! was looking for this for a long time!

  8. #8
    New Member
    Join Date
    Dec 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract web address

    Thank you so much Russell!

    For those looking how to create a custom function, check http://office.microsoft.com/en-us/ex...010218996.aspx

    Short instructions are:

    Press Alt+F11 to open the Visual Basic Editor, and then click Insert, Module.

    In the new module, type the code from Russell above

    Then save

  9. #9
    New Member
    Join Date
    Aug 2011
    Location
    Greenbank, WA, US
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract web address

    I am running Excel 2007, running a macro-enabled workbook. Whether I paste the function direct from the site, or via a paste-to-notepad-then-copy-to-VB, or when typed by hand, I get the following result when I call the function: "#NAME?" The function indicated it takes no arguments, despite the function appearing to be correctly cast (no added spaces, using different name in case of conflict, etc).

    The MSKB articles come close but do not hit the problem. I am at a bit of a loss for my next step, short of running to my O'Reilly library and taking a crash course on VBS, which would probably be good in any event.

    Thanks!
    sh

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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