+ Reply to Thread
Results 1 to 5 of 5

Function to create email hyperlinks for multiple cells?

  1. #1
    Registered User
    Join Date
    12-04-2008
    Location
    Paris, France
    Posts
    3

    Function to create email hyperlinks for multiple cells?

    I have a column with about a thousand email addresses written as plain text (one per cell obviously). Is there a way to hyperlink these without having to go through them one by one?

    Thanks!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,444
    Hi,

    Presuming your list is in column A, starting at A1,

    enter =HYPERLINK(A1) in B1, and copy it doen the length of data.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    12-04-2008
    Location
    Paris, France
    Posts
    3
    Thanks sweep, but I've already tried that.
    Excel doesn't recognize the text ("[email protected]") as an email address so the hyperlink directs to "[email protected]" and not "mailto:[email protected]".
    Any other suggestions?

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,444
    How about

    =HYPERLINK("mailto:"&A1)

  5. #5
    Registered User
    Join Date
    12-04-2008
    Location
    Paris, France
    Posts
    3
    It worked, thanks a lot sweep.
    I actually did the following to only keep the address displayed as text in the cell (without the "mailto:"):
    =HYPERLINK("mailto:"&A1,A1)


    Just in case someone has the same problem and comes accross this topic, I also found this link:
    http://www.experts-exchange.com/Micr..._23750529.html
    It uses a macro, which is a more complicated way to obtain the same result. However, if there are blanks in the column (empty cells i.e. with no email addresses), this macro keeps the cells blank whereas the hyperlink function creates a "0" link (that links to "mailto:").

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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