We help IT Professionals succeed at work.

How to format column cells as hyperlink

Jim Horn
Jim Horn asked
on
Hello Excel Gods and lesser demi-gods

I have an Excel 2010, 32-bit spreadsheet with a connection to SQL Server where one of the columns / 500ish rows is a URL.  Works fine.

Question:  How can I format a  column as Hyperlink, so that when the user clicks on the cell it goes to the URL in the cell value?

When I click on the whole column and do a right-click:Format Cells, Number tab, I don't see url/hyperlink as a choice.

Thanks in advance.
Jim
Comment
Watch Question

Jeff BrownGlobal Helpdesk Supervisor

Commented:
Can you give an example of url type you are using.   Excel generally does this automatically if it is a standard  www.news.com format.  Some types of lengthy urls will not automatically get converted.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Sure, they're all salesforce.com links.  Example (mocked up, so won't work)
https://sg42.salesforce.com/001B000004BE1KRZA1
Global Helpdesk Supervisor
Commented:
great ok for something like that you can use this code at the beginning to turn it into a link

=HYPERLINK

another way to do this

If you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.

In the new column type in the formula =HYPERLINK(A1) (replacing A1 with whatever cell you are interested in). Then copy the formula down the rest of the entries
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Thanks.  This actually worked out better, as I was able to use HYPERLINK() to pass the member name as text AND the url.

First column in the below SQL Server SELECT statement is how I was passing it, third column is how I'll pass it now.
SELECT
   'https://sg42.salesforce.com/' + mem.id as url, 
   mem.Member_ID__c, 
   '=HYPERLINK("https://sg42.salesforce.com/' + mem.id + '", "' + mem.LastName + ', ' + mem.FirstName + '")' as MemberName, 
   mem.Plan_ID__c, 
   mem.Sub_Plan_Text__c, 
   mem.HICN__c, 
   mem.Medicaid_ID__c, 
   eng.Program_Start_Date__c
FROM SF_Account_c mem
   LEFT JOIN SF_Engagement_c eng ON mem.id = eng.Account__c
WHERE mem.HICN__c IN (
   SELECT HICN__c FROM SF_Account_c WHERE HICN__c IS NOT NULL GROUP BY HICN__c HAVING COUNT(id) > 1)
ORDER BY mem.HICN__c, eng.Program_Start_Date__c
GO

Open in new window


Thanks.
Jim
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Hmm.  When I refresh my connection, it displays the entire function in the cell...
=HYPERLINK("https://sg42.salesforce.com/0011200001A64NGAAZ", "BIZUP, MIRIAM")

Open in new window

... and it's only when I manually go into the cell and hit <return> that it displays as just lastname comma firstname as a hyperlink.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
I asked the above as a new question here.