=hyperlink formatting bug

LO x64
Win 7-64
=HYPERLINK(URL,text) produces Err:t10 when uncorrected and when corrected. The LB corrected URL removes one of the forward slashes (https// becomes https:/).

ctl-k URL text changes formatting from currency to text. Clone formatting is unable to change from text to currency. Format->Cell->currency also fails.

All of the suggestions from [Hyperlink removes cell currency format] also fail.

Clone_Formatting.ods (13.0 KB)





I followed these steps for $B2, $B3 ;

  1. Clicked in fx
  2. Selected HYPERLINK from the search box
  3. Double clicked on HYPERLINK
  4. Entered URL https://www.slipbits.com
  5. Entered Cell Text “1234.56”
  6. Clicked on"OK"

The result in each cell was "=HYPERLINK( https://www.slipbits.com,1234.56) and an error (Err:510). Using LO to fix the error yields "=HYPERLINK( https:/www.slipbits.com,1234.56) (removed one ‘/’) and retained the comma separator. The fix is invalid http syntax, and generates another error (Err:510). Why does the ‘fix’ generate illegal syntax for the URL? Why does LO generate an incorrect hyperlink argument separator in both cases?

Following your suggestion, “=HYPERLINK(URL;text)” was used instead of “=HYPERLINK(URL,text)”. It yields the same error sequence.

In both cases there is a question as to why an LO suggested fix produces an error. Why doesn’t the fix ‘fix’ the error?

On my Win7-64 system it just doesn’t work. Sometime today I will try it on my Win10-64 system and see if the error remains. Right now I’m stumped. I don’t know why your solution works on your machine and not mine.


Whoops. I didn’t see the =HYPERLINK(“URL”,text). Changing the URL to “URL” solves all the issues. That is, there is no error message, the generated hyperlink is correct, and I can clone my currency format to yield the correct format for the cell.

But, there still remains the issue as to why a LO fix is not a ‘fix’, and a new issue as to why the generated URL is not surrounded with double quotes. The URL entry in the HYPERLINK function does not specify any quoting, does not generate any quoting, and does not indicate that the input URL is incorrectly formatted. I think that that’s a bug.

The use of a comma or semi-colon yields the same result. They appear to be identical in operation.

Thanks for helping. I’m sorry that I didn’t see the “URL” in times.

What could that possibly mean? What does one do to “use LO to fix”?

Anyway, Calc can only see that your expression used for the first argument has illegal syntax (like using two divide operators), not that it doesn’t make logical sense. If your expression takes the form a / b, it’s OK from Calc point of view, but would possibly fail if there’s no a or b name defined (Calc can’t know if you meant e.g. some named range), or a can’t be divided by b (e.g., because they are not numbers), or the result makes no sense as the first argument to HYPERLINK function.


Sorry about the late response. Life intrudes.

I like LO and have liked it since I first began using it. So my comments are about a product I like, they are not a criticism of the product or the many hardworking people that have made the product successful.

Let’s look at what I see when I click on fx->HYPERLINK. I see two text boxes, URL and Cell text. Typing in a correctly formatted URL gives me an error. To me, looking at this, I see that I have followed directions and provided correct input, but my input is not correct and yields an Err 511 in the Result text box. I then go to LO 7.2 help for HYPERLINK and see
HYPERLINK(“URL” [; “CellText”])

The URL is not what fx->HYPERLINK asked for, it’s “URL”. So this is my question. Why doesn’t LO use the legend “URL” for the input text box, automatically insert the double quotes (") as appropriate, or eliminate the double quotes as a valid URL? Giving an error indication (Err 511) for an appropriately formatted input is confusing.

A similar issue exists for text. If you input text you get a #NAME Result, and error. If you input “text” it works correctly. Why not eliminate the double quotes (") or provide a legend, Cell “text” or insert double quotes surrounding the text as needed?

For ctl_k it is even more confusing. ctl_k does not act like =HYPERLINK() (this has been brought up in this messaging stream). Instead, ctl_k allows both double quoted and non-quoted parameters. But there is another difference. The double quotes surrounding the text (“text”) is visible. But in =HYPERLINK() the double quotes surrounding the text (“text”) is not visible.

In ctl-k and =HYPERLINK() when the text is a number double quotes are not necessary.

One last thing, in =HYPERLINK() a double quoted number (“1234”) can be formatted, I tried Format->Cell->[currency and number], but in ctl-k the cell can not be formatted. This behavior is not documented.

Couldn’t ctl-k and =HYPERLINK() be constructed so that their functioning is identical? And why does “text” need a double quote but number (1234) not need one? Shouldn’t the input formats be the same?

The “hyperlink” help file has some issues. Nowhere does it identify when double quotes are needed, and although the functionality is global, no constraints on use by LO category (calc, writer, …) are mentioned.

So my thought is that there is a bug in the help file, text box legends, or implementation, that there is no way to determine what ctl-k does different from =HYPERLINK(), and it should do the same thing as =HYPERLINK(). I do note that there is an Insert->Hyperlink (Ctl-K)

So I get confused.

Evaluating the URL in a calc cell provides another point of confusion. My guess is that the cell parser does not look at context, so when it sees “//” in “http://URL.com” it says that there is no valid arithmetic construct which has “//” in it, therefor this is wrong and the correct solution is to remove one of the forward slashes. But this is not an arithmetic construct. It is a parameter to the =HYPERLINK() function. Ignoring this leads to an incorrect assessment of fault and an incorrect display of a fix. Then, after the fix, calc attempts to evaluate =HYPERLINK() and finds an ill-formed URL So if you don’t fix it, it’s an error, and if you do fix it, it’s an error. I think that this is a bug. The cell parser should evaluate cell contents in the framework of any functions. When a function is seen, argument evaluation should proceed according to function requirements.

Now this all is probably a lot of work. But if you accept the notion that something should be done, then put it onto either the BUG list or the ENHANCEMENT list. Sooner or later it will be time to address it. Otherwise, oh well, LO is a great product w/wo any modification.


The quotes there are not to appear in the attribute, they are for reader to understand that the function expects a string here. And strings may be provided by many different ways, e.g., literal strings (what you expect), or as formulas (say, "#" & ROW()), or as references ($B12), etc. The URL may be not only absolute, but also relative, and it may be OK for someone to rely on Calc to convert some number to string to obtain a correct relative reference. So no, Calc should not try to outsmart user and insert quotes around user input.

I’m really confused how could one ask “why does “text” need a double quote but number (1234) not need one?” - how else would you tell number 0123 (which is equal to 123) from text "0123" (which would not lose the 0), or text "1+2" (that must be displayed as is) from formula 1+2 (that must yield 3)?

Just to illustrate how it is completely wrong: you imagine that every argument is just a simple literal value; you imagine something as simple as


while it’s perfectly OK (and actully much more widespread use of the function) to look like


or some such, where the function takes its argument by calculating something in the nested complex expression, potentially consisting of more nesting, involving functions and operators. E.g., the A1 and B1 may include partial URLs and article ids, that would combine into the full URL that would go to the article corresponding to the row 1; extended to the rest of the rows, the formula would give the respective URLs for the rest of articles. Use of fixed literal, as you suggest, is rather rare corner case, not really useful in spreadsheet software, that is about mass processing of similar data.

Because you simply show complete lack of knowledge about what spreadsheet is, and how it works, and which feature exist there. When you put text as the second argument to HYPERLINK, it’s completely valid, and may refer e.g. to a named range, or an automatic name; the range use in such formulas is immensely useful; and Calc has no way of knowing that when text doesn’t match an actually existing range, it was user’s intention to type literal "text", not just a typo in the range name - maybe user wanted to type text1, which would be found?

Sigh. Complete mess of misunderstanding. Ctrl+K is a common functionality to insert field. HYPERLINK is a spreadsheet function (that, by the way, may not only return string as its second argument, but also numbers if passed there, which may then be used in calculations); use of one is completely unrelated to the other; use of software implies some learning curve, and wish to open some complex software and start creating worders of professional documents with it without any learning is a lunacy. We have tons of documentation, but indeed, it’s not useful for people, because they ignore it.

1 Like


Thanks. I just checked

  1. =HYPERLINK(“https://www.example.com”, “123456”) . No double quotes around the number. Could not change from a text to a number. I guess this is ok.

  2. =HYPERLINK(“https://www.examp.com”, 123456). Showed as a number.

  3. ctl-K URL “https://www.example.com” Text “123456”. Double quotes around the number. Could not change from a text to a number. Double quotes around the number. Could not change from a text to a number.

  4. ctl_K URL https://www.example.com Text “123456”, Double quotes around the number. Could not change from a text to a number.

  5. ctl-K URL “https://www.example.com” Text 123456. No quotes around the number. Could change from a text to a number.

  6. ctl-K URL https://www.example.com Text 123456. No quotes around the number. Could change from a text to a number.

There are inconsistencies between =HYPERLINK and ctl-k. My hope is that this is seen as an enhancement (change) and changed so that the behaviors are the same.

  1. =HYPERLINK never quotes Text. ctl-k quotes quoted text.
  2. =HYPERLINK will only accept a quoted URL. ctl-k accepts quoted and unquoted URL’s
  3. In neither case can a quoted Text field containing a number be converted to a number. This is consistent with current behavior. It would be ‘nice’ to allow Format->Cell->Number to change a numeric text string to a number (and back to a text string)

I Hope this table comes out ok.

           |          |          |      Result         |
           |   URL    |   Text   | quote  | conversion |
=HYPERLINK | quoted   | quoted   | No     | No         |
=HYPERLINK | quoted   | unquoted | No     | Yes        |  
=HYPERLINK | unquoted | unquoted | Err:510| ---        |
=HYPERLINK | unquoted | quoted   | Err:510| ---        |
ctl-K      | quoted   | quoted   | Yes    | No         |
ctl-K      | quoted   | unquoted | No     | Yes        |
ctl-K      | unquoted | quoted   | Yes    | No         |
ctl-K      | unquoted | unquoted | No     | Yes        |

 =HYPERLINK fx->HYPERLINK->double click was used
 ctl-K      ctl-k was used
 URL        Input URL field value
 Text       Input Text field value
 Result     Result after OK was clicked
 quotes     Did the Result have double quotes
 conversion Was Format->Cell->Number (dec. 2, thousands) successful

Thanks for spending time on this. I appreciate it.

No. There may be no inconsistencies between completely unrelated features.
HYPERLINK is a spreadsheet function. It follows spreadsheet formula syntax, including how text and number is defined in formulas.
Ctrl+K is a dialog to define text attributes (insert a field). It has nothing to do with formulas; it is not even specific to Calc; it is about text and its properties in all modules. It accepts two literal strings in its text boxes.


The bottom line: you think you found some “inconsistency”, but trying to implement your idea, you would replace your personal confusion coming from being a novice, into huge inconsistencies of either a spreadsheet function with the rest of spreadsheet functions, or a dialog with the rest of the dialogs, and that inconsistency would hit millions.

1 Like

Your constant misreading of my comments is a sign of your lack of ability to understand the issues. You are, in a word, obtuse. Please do not answer any of my questions again. It serves no purpose to me and only serves to inflate your feeling of self worth.

And with that someone was welcomed on my ignore list.