+ Reply to Thread
Results 1 to 15 of 15

Convert Hyperlinks to text

  1. #1
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Convert Hyperlinks to text

    I have an excel 2007 worksheet that has thousands of lines. All of them include at least one link to an email (email). I need to remove the hyperlink and show the actual emails for sorting purposes. I can do it manually but it will take hours. Can anyone help with a macro or VBA formula?
    Last edited by breet77; 12-22-2012 at 05:36 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert Hyperlinks to text

    hi breet, welcome to the forum

    Im sure some-one can help, but it would help us to help you if you provided a few exmples of what you are dealing with, and what you're expected outcome would be?

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    File attached. Column 'R' is the one that I am having difficulty with. rather than saying email and being a hyperlink I need it to show me the actual email. It does not matter if the email is a hyperlink or text.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Convert Hyperlinks to text

    With the hyperlink in f1

    g1
    Please Login or Register  to view this content.
    and drag down
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Convert Hyperlinks to text

    Hi,

    In Excel 2010 you can highlight the whole column, right click and select "Remove Hyperlinks". Not sure if this works in 2007 or earlier (Been a while since I used an earlier version).

    In your sample file, one way of getting rid of the hyperlinks by entering =R2 in cell T2 and copying down. Then paste special/values over that column and then copy and paste column T over column R.

    I'm sure there are more eloquent ways of doing it, but if it's a one off task then this takes just a couple of seconds.
    If I've been of help, please hit the star

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert Hyperlinks to text

    not sure why you want to remove the HL, i was able to sort that sheet, based on the email addy, just finer.

    however, if you want to remove the HL, in a cell (ot the right?) enter =R2 and copy down. then copy/paste values those formulas to themselves (highlight the range, ctrl C to copy, on the home tab, select the "paste" pull-down and select paste values)

    if you want, you can delete the original HL data, or you can keep it, up to you

    let me know how you make out

  7. #7
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    Just removing the hyperlinks leaves me with the word email and not the actual email. In addition to sorting purposes I am also creating mass emails to specific positions. Like all the Baseball coaches. Mass emails do not work with the hyperlinks.

  8. #8
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    I can sort all columns but when I sort by the email column it does not actually sort based upon the email that is hyperlinked.

  9. #9
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    I know this can be done. I had a professor of mine look into it once before. Sadly he just did it for me and did not tell me how. He is unavailable at the moment due to our Christmas break.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Convert Hyperlinks to text

    did you upload the correct file then?

    column R2 [email protected] hypewrlinked
    if you =R2, it gives you teh exatct same thing, but ithout the HL

    and as i said, when I sorted the table, based on column R, it sorted just fine. see attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    the first couple of lines have already been converted manually. (Through R6) It's after that where I need help. I once converted 12,000 lines manually and then asked for help. I didn't think that I would be doing this again or I would have insisted that he show me how rather than just do it for me. Sadly, like I said above, he is not available to help this time.

  12. #12
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    I have looked elsewhere and this is what I found. I am not even going to pretend to understand the coding involved in this. I created the user defined module using the following code but when I try to use it all I get are errors.

    Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace _
    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
    End Function

    To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

    =GetAddress(A1)
    Where cell A1 has a Hyperlink within it

  13. #13
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    thank you all for your input. I found something that will work for me. IT removes all hyperlinks but I don't need any of them.

    VB code

    Sub RemoveLinks()
    Dim hl As Hyperlink, s As String, r As Range
    Application.ScreenUpdating = False
    For Each hl In ActiveSheet.Hyperlinks
    s = hl.Address
    Set r = hl.Range
    hl.Delete
    r.Value = s
    Next hl
    Application.ScreenUpdating = True
    End Sub

  14. #14
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Convert Hyperlinks to text

    see this
    http://www.bohack.com/2009/06/get-th...link-in-excel/
    you can get rid of the mailto: with
    =SUBSTITUTE(GETURL(R2),"mailto:","")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  15. #15
    Registered User
    Join Date
    12-22-2012
    Location
    Nampa, Idaho
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Convert Hyperlinks to text

    Thank you! It will be quicker to not have to use the find and replace function, although that did work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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