Fixing HYPERLINK functions in Excel and OpenDocument Exports
Update: as of version 2.10.13, HYPERLINK functions should work in downloaded Excel .xlxs files automatically.
If you have a form that includes uploaded files, then
.xlsx and .ods export files will have HYPERLINK functions for each file entry. This is intended to enable you to download the files.
But the problem is that the export code doesn’t support creating functions. So the literal text of the function appears in the cell, but
Excel, OpenOffice & LibreOffice don’t know that it is a formula.
The fix for this is:
- Open the export file
- Select the cells with formula text
- Do a “Find/Replace All” operation: find “=” and replace with “=”
That doesn’t change the formula but tricks
Excel, OpenOffice & LibreOffice into re-evaluating the cells at which point it converts them to an actual formula. In Excel selecting a single cell and pressing F2-Enter will convert the cell to a formula. But this only works on one cell, not a range of selected cells.