Special Characters In Hyperlinks

You probably already know that Excel allows you to create hyperlinks to other Excel workbooks. If you create a workbook that uses the pound sign (#) in the file name, Excel has no problem with that. It will have a problem, however, if you try to create a hyperlink that references a workbook with a pound sign in the file name.

The reason for this is because the pound sign is a valid character for a file name, but it is not a valid character for use in a hyperlink. Since hyperlinks are closely related to URLs, you may think that replacing the pound sign with its hexadecimal equivalent (%23) in the hyperlink might do the trick. For instance, you might use the name My%23File.xlsx in the hyperlink instead of My#File.xlsx. This potential solution won't work, however. Excel still complains that it cannot find the file when you click on the hyperlink.

According to Microsoft sources, there are only two potential solutions. The first is to rename the target workbook so it doesn't include the pound sign in the file name. If this is not possible, then the second solution is to create a hyperlink by pasting instead of by using the Insert Hyperlink command. Follow these steps:

1. Open the workbook in which you want the hyperlink.
2. Open the target workbook.
3. In the target workbook, select the cell or range of cells you want selected when the hyperlink is clicked.
4. Press Ctrl+C to copy the cell or range of cells to the Clipboard.
5. Activate the workbook in which you want the hyperlink.
6. Select the cell where you want the hyperlink to appear.
7. Display the Home tab of the ribbon.
8. Click the down-arrow under the Paste tool and then choose Paste As Hyperlink.

Your hyperlink appears, complete with the pound sign, and it will work.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12572) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Special Characters In Hyperlinks.