How to replace reference to another cell with the cell's content?

[chandles]

OMG this is frustrating I know someone has had to ask this before but my english isn’t working, or Google isn’t working. This isn’t that complicated but guess it’s confusing to describe.

I have some cells in column A with data in them (URL’s in this case). In B1 I used the formula =HYPERLINK(A1,"Click here") and that works and I get a working “Click here” hyperlink that Ctrl-Click opens to https://www.example.com since the content/value of A1 is https://www.example.com.

Now I want to expand or evaluate the reference used in B1 to A1, so the resulting formula will be =HYPERLINK("https://www.example.com","Click here"). How to do? When done, A1:A10 are no longer needed and can be discarded.

Thanks

[JohnSUN]

Do you mean F2-F9-Enter? Or Find and Replace?

[chandles]

When I try F2-F9-Enter, it turns the cell from:
=HYPERLINK(A1,"Click here") to:
{"Click here";"https://www.example.com"}


Problem seems to be caused by the double quotes that are needed in the hyperlink() function.  Using unichar() solved that.  This produces the correct text:
=CONCAT("=", "HYPERLINK", "(", UNICHAR(34), A1, UNICHAR(34), ",", UNICHAR(34), "Click Here", UNICHAR(34), ")")


However, it evaluates that and displays it literally as:
=HYPERLINK("https://www.example.com","Click here").  Your F2-F9 trick is needed after to produce the desired final result of showing up to the user as
Click here
in the cell.


Is there a way to do that in one step, without F2-F9 afterwards? or automate?  As it is, have to do F2-F9-Enter on each cell individually.


Also, I thought there was a way to just do it with copy/paste? but maybe confusing this with some other trick.


Regular expressions are voodoo to me.  You are on to something, however the resulting formula when I do the replace is:
=HYPERLINK("=HYPERLINK(A1,","Click here") "Click here")


Thanks

[Lupp]

For hyperlinks based on literals instead of references (at least one) Calc has a special textfield type.
As far as I can see there is no way using predefined tools to replace the existing formula by a reworked one containing the URL as a literal instead of referencing it.
You will need user code (a “macro”).
But why then the formula? The macro can create the com.sun.star.text.textfield.URL .

[JohnSUN]

Oh, I wasn’t trying to give a “Do it like this” answer.

@chandles

I know someone has had to ask this before

You complained that you couldn’t remember the method and I simply asked again which method you were asking about.
I know of two reliable methods and have provided links to both. It’s a shame you weren’t able to take advantage of these recommendations.
I’ll explain in detail what I meant.
F2-F9-Enter is not something you had to press, it’s the name of the principle. If you look carefully at this screenshot, you will probably understand what I meant:

“Enter cell editing mode. Select the part of the formula that you are going to change (in this case, the address of the cell with the link), press F9 to calculate this part of the formula, press Enter to insert the calculated value instead of the cell address, finish editing the formula"

If you had understood the hint, then the Find and Replace technique would have worked correctly for you. Let me hint once again: this replacement should be applied not to formulas with the HYPERLINK() function, but to cells with link addresses (in your case, to the range A1:A10)

[erAck]

@JohnSUN

this replacement should be applied not to formulas with the HYPERLINK() function, but to cells with link addresses (in your case, to the range A1:A10)

That might come across misleading; for the initial request with example =HYPERLINK(A1,"Click here") select the A1 part in the formula, press F9, "https://www.example.com" will be displayed as tool-tip, hit Enter, the selected A1 will be replaced by "https://www.example.com".

[JohnSUN]

@erAck

That might come across misleading

Yes, I agree - it is not formulated very correctly. We are talking about the second way to solve the problem - through Find and Replace: the =HYPERLINK() formula in column B is not needed, it will be immediately created in the range A1:A10.

[Lupp]

I can’t shake the suspicion that this is a blatant case of the XY problem.
I would therefore ask the OriginalQuestioner to explain his(f/m) concept, and for what reasons she(m/f) thinks the step discussed here is needed or useful.

As described here by the subject, the question is too special to justify this lot of considerations. It needs to be extended because an argument position should be expected to be used for an arbitrary expression.
On the other hand, allowing for expressions would obviously require a complete discussion of the “evaluate-function-problem”.
IIRC there were already many attempts insofar, but no solution.