LO 7.2.5.2 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)
Use
=HYPERLINK("https://www.slipbits.com";1234.56)
not
=HYPERLINK(https:/www.slipbits.com;1234.56)
@sokol92
I followed these steps for $B2, $B3 ;
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.
thanks
@sokol92
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
Syntax
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.
art
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
=HYPERLINK("https://www.address.tld/a/b.c?d=e#f")
while itâs perfectly OK (and actully much more widespread use of the function) to look like
=HYPERLINK(IF(ISBLANK(A1);B1;LEFT(A1;5)+(B1+12)))
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.
Thanks. I just checked
=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.
=HYPERLINK(âhttps://www.examp.comâ, 123456). Showed as a number.
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.
ctl_K URL https://www.example.com Text â123456â, Double quotes around the number. Could not change from a text to a number.
ctl-K URL âhttps://www.example.comâ Text 123456. No quotes around the number. Could change from a text to a number.
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.
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 |
LEGEND
=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.
art
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.
No.
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.
@mikekaganski
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.