Excel formula to hyperlink ms-help URLs to the Web

  • Article

This helpful tip comes courtesy of Matt Stroshane, a Writer on our team.

 

I made a little Excel formula that you might find useful when using Excel for researching or otherwise tracking help topics. This Excel formula displays the topic GUID as a hyperlink to the Web-based equivalent topic on MSDN Online. When there is no text in the ms-help URL cell, it just displays "-" as to avoid the bad reference "#VALUE!" error.

 

Procedure

1. Copy the ms-help URL out of Visual Studio Help or BOL and paste it to an Excel worksheet cell.

2. Reference the cell containing the ms-help URL from step #1 with the formula to get your hyperlink. (the example lists the formula)

 

Example

Let's say you have BOL open, and you want to note a topic in your spreadsheet. Copy the topic URL shown in the help program's "address bar." The URL will start with "ms-help://". Then, paste the URL to your spreadsheet, cell E5 in this example:

ms-help://MS.SQLCC.v10/s10cc_Main/html/674933a8-e423-4d44-a39b-2a997e2c2333.htm

 

Then, you decide you want the GUID hyperlink in cell D5. Select D5, then paste the following command into the cell:

 

=+IF(E5<>"",HYPERLINK("https://msdn.microsoft.com/en-us/library/" & MID(E5,LEN(E5)-39,36) & ".aspx", MID(E5,LEN(E5)-39,36)),"-")

 

After you hit enter, the cell should display a hyperlink that looks like this (in Outlook, hold down Ctrl+Click to follow the link):

674933a8-e423-4d44-a39b-2a997e2c2333

Note: It may not "look" like a hyperlink because the link is surrounded by the IF statement to avoid the ugly #VALUE! error. They say beauty is in the eyes of the beholder… if you find the "look" of a hyperlink more attractive, remove the IF statement, and instead use the formula:

 

=+HYPERLINK("https://msdn.microsoft.com/en-us/library/" & MID(E5,LEN(E5)-39,36) & ".aspx", MID(E5,LEN(E5)-39,36))