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 67
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.