I have a cell formatted as ‘currency:0 decimal pts’. When I create a hyperlink with the cell text (ctl_k with currency highlighted), the format remains ‘currency’ but the currency symbol ($) and the thousands separator (,) is removed, and the alignment is changed to ‘left justified’. I can change the alignment but I can’t seem to get the currency symbol and thousands separator restored (Format->Cell->Currency). Is that anything that I can do about this?
Please upload your ODF type sample file here.
Well it is a dilemma, Currency is a number format and hyperlinks are text format; the contents become text.
The best way is to have the currency in one cell and the link next to it.
To pretend it is still currency:
If you select the text in the cell and press Ctrl+K then you can add the currency symbol, thousands separator and decimal point (if wanted) in the Text field. The cell will look like currency but it is not a number and cannot be added to or multiplied by another number. The Left alignment indicates the contents are text but that too can be changed. Cheers, Al
1 Like
Umm. I don’t know how to do that. I don’t know where the upload link is.
Notice that menú Insert - Hyperlink… is not the same as the function HYPERLINK.
Commons steps to both options:
- Secondary click EDIT: on the number /EDIT in the cell M4¹
- Choose
Edit Hyperlink…
The first option only works for the number format, not for the hyperlink; I should have checked a bit more. Sorry. First option (the short one): Edit the Hyperlink text:
- Add in the text field
=NUMBERVALUE(the number) PressEntertwice
Second option: Use the function HYPERLINK:
- Cut the Text: “3798”
- Click
OKor pressEnter - Press
End - Type
"; - Paste (the “3298”, without quotation marks)
- Close parentheses
- Press
Ctrl+Home - Type
=HYPERLINK(" Enter- Clone Formatting from cell F9
¹ Alternativelly select the cell content (F2 and Shift+Home, then Shift+F10 or the context menu key)
Tested with LibreOffice 7.1.8.1, on Windows 10.
EDIT: See Refrigerator LeroyG.ods (21.3 KB) (changed Split Window to Frezze Rows and Columns)
Using LO 7.2.5.2
I tried both options several times. Neither works.
- The first option changes the URL to "file:///J:/tmp/Appliances/refrigerators/3298, a non-existent file on my CPU.
- The second option changes HYPERLINK to =NUMBERVALUE(3798), the URL is removed.
Use the HYPERLINK() function instead, like
=HYPERLINK("https://example.com";12345.67)
or
=HYPERLINK("https://example.com";A1)
The result type of the displayed value will be the type of the second argument, so for numeric you can format it as currency as well.
1 Like
=HYPERLINK("https://example.com";TEXT(A1;"[$$-C09]#,##0.00;-[$$-C09]#,##0.00"))
You can copy some predefined format code from the cell formatting dialog.
The TEXT() is unnecessary, or rather harmful. As said, the second argument to HYPERLINK() will be the result that if numeric can be formatted as usual with the cell’s number format. And also used in further calculations, which if it was converted to text is not possible.
1 Like
HYPERLINK does not work, as in =HYPERLINK(https://www.homedepot.com/p/LG-Electronics-23-3-cu-ft-French-Door-Smart-Refrigerator-InstaView-Dual-Craft-Ice-PrintProof-Stainless-Counter-Depth-LRFVC2406S/310338070,3798). I get a “Err:510”.
I have tried typing =HYPERLINK() and using the ‘fx’ functionality with the same result
Literal strings in formulas have to be in double-quotes.
1 Like
=HYPERLINK(URL,"“number”") yields the same result - “Err:510”.
You don’t want the number to be a literal string, you want the URL to be a literal string.
=HYPERLINK("https://www.homedepot.com/p/LG-Electronics-23-3-cu-ft-French-Door-Smart-Refrigerator-InstaView-Dual-Craft-Ice-PrintProof-Stainless-Counter-Depth-LRFVC2406S/310338070";3798)
1 Like
Put the calculatable number in one cell, any kind of text in another cell and any formula belongs into a third cell.