Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    How does one force hyperlink formatting in an Excel spreadsheet?

    Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses.

    All of the addresses appear as hyperlinks, i.e. blue and underlined, but some occasionally seem to lose their hyperlink properties, although they remain blue and underlined. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on them does not launch the browser (or email client).

    Is there any way to ensure they are formatted, and work, as hyperlinks please?

    Thanks,

    V

    PS Occasionally when I paste in new addresses, the data remains black and does not show as hyperlinks...

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Excel attempts to format URLs as hyperlinks, but it is not always successful. If you have a hyperlink you can right click it and remove the hyperlink, leaving the formatting - blue and underlined. You can also remove the formatting but leave the hyperlink.

    The only way to check if all cells have hyperlinks would be to write a macro that tests a particular range of cells. Then it can add a hyperlink if one doesn't exist and maybe change the formatting so you know it's been checked.

    cheers, Paul

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    A couple of thoughts
    1) to force Excel to pick up the fact that the address you've just pasted needs a hyperlink, try pressing the F2 button after you've pasted. Sometime a cell needs editing to 'wake up' to the hyperlink. Also an address pasted with extra spaces at the end sometimes won't hyperlink - so you may need to remove them.
    2) If you have a column of email addresses, which you want to force to by hyperlinked, try the hyperlink formula.
    I'd insert a column and use the following formula: =HYPERLINK("mailto:" &A1,A1) (where the cell A1 contains the email address).
    For web addresses the formula might look like =HYPERLINK("http://"&A1,A1), if you have been entering web addresses without the http.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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