How to format column cells as hyperlink

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
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
Jeff BrownGlobal Helpdesk SupervisorCommented:
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
0
 
Jeff BrownGlobal Helpdesk SupervisorCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Sure, they're all salesforce.com links.  Example (mocked up, so won't work)
https://sg42.salesforce.com/001B000004BE1KRZA1
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor 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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor 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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I asked the above as a new question here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.