Extract Hyperlink

[worltraveler]

Looks like it's been a few years since this was asked... hoping 4.1.3 will have addressed this.

I've searched for a couple of hours on extracting the "hyperlink" "link" URL contents from a cell to another. Like "text to columns," but in the original column is the readable description, which when you click on goes to the destination URL in my default browser.

I am amazed that this is not a simple built-in function! My hyperlink is in Column D (I can only see the description, I have to hover over to see the URL). I want to extract the URL (hidden) to Column E. I have about 450 rows.

It is possible that in the time I've gone looking for this, I could have right-clicked Copy Link Address from Col D and pasted into Col E, 450 times! But there must be a way!

Comparing to older posts, like others, every attempt I've tried with cell_url(...) and hyperlink(...) results in #ref or #value.

[Lupp]

I don't think the situation has changed. There is no built-in function to extract URL connected to parts of the text in a cell.

If you can accept a user defined function for the purpose you may still use the

Function CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)

published by "Villeroy" in his 'Code Snippets' thread [Calc, Basic] Introspective cell functions.

[worltraveler]

I wrote my first COBOL program in 1973, and have done PHP since 2005 (.html and DreamWeaver prior)...

I sorta got it to work! =CELL_URL(SHEET();ROW();4)

Except I can't Edit > Fill Down,

nor Select a bunch of Cells and Paste!

I have to select one Cell, paste, enter (which then drops to the row below), then paste, enter! ...

450 times.

[Lupp]

Did you ever handle an AOO spreadsheet or one from another brand?

[worltraveler]

Do not understand abbreviation nor point.

[Zizi64]

Except I can't Edit > Fill Down,
nor Select a bunch of Cells and Paste!
I have to select one Cell, paste, enter (which then drops to the row below), then paste, enter! ...
450 times.

If the formula located near the input data of the formula, just click on the little square located in the right bottom corner of the cell containing the first formula:
The formula will be copied down automatically.

[worltraveler]

OK, but no "little squares" on mine.

[Zizi64]

See the little black square at the right bottom corner of the cell B1. (Apache OpenOffice 4.1.3 on Windows 7)

[worltraveler]

Pasting =CELL_URL(SHEET();ROW();4) into the Cell... then Selecting that Cell and dragging the little black square (creating the red outline) only copies the result of the "=" formula in the first Cell, not the formula itself.

[Zizi64]

Please upload your ODF type sample file with the first formula, and with the embedded macros here.

then Selecting that Cell and dragging the little black square (creating the red outline) only copies the result of the "=" formula in the first Cell, not the formula itself.

No drag. Double click on the square.

[worltraveler]

Again, copied the result URL from the Cell selected to all the rows below it with data in them.

Long ago I thought I should revert to MSOffice.

But, I am Mac, so I could do Apple Numbers. blah.

Google has "Docs" which a lot of people use. Not sure if appropriate, but since I travel, and store some of my OO files in iCloud anyway... if I were to expend "learning curve" energy, it might be in that path.

All this paste/Enter made my wrist sore, but it got done.

I do need to make sure my new Library is protected on the next OO update - which should be upcoming??

My biggest complaint about Calc is any time I try to type a number >10 (or so) digits, it converts it to Scientific Notation (even though the Cell was formatted as "@ text"... or copy/paste or type digits with '-' or '/' and it keeps converting to "date" or a different variation of "date," or the Epoch number for the date in question. Just a pain. ISBN-13 numbers are not Scientific Notation. If I type 2017-07-03, I want it left alone, not converted to other variations of "date," which will then mess with PHP as data. I sure have to type a lot of "'"s in the first position of a cell with newly acquired data.

[Villeroy]

Like all other kinds of ID numbers, ISBN "Numbers" are text and they should be entered as text. This is a plain simple spreadsheet program like half a dozend of other spreadsheet programs that were originating from the 80ies. There is nothing special with Calc. It behaves (almost) exactly like all the other spreadsheet programs and you have to know a few very simple concepts when using spreadsheets. [Tutorial] Ten concepts that every Calc user should know

The formula =CELL_URL(SHEET();ROW();4) with my user-defined cell function works as designed. It extracts the URL of the first hyperlink of the cell in column 4 in this row on this sheet. Like in Excel there are at least 3 methods to fill down a formula. The fill command, draging the cell handle and copy/paste. Any of them copies the formula.

I do need to make sure my new Library is protected on the next OO update - which should be upcoming??

OO is a dead project. We may never see any more updates beyond version 4.1.3. https://libreoffice.org/ is the legitimate successor since 2010. Both programs keep their user settings in a folder (the so called user profile) even when you remove the program.

[worltraveler]

I can tell it's "dead," and not me! I can look into Libre, otherwise maybe it's time to look at Docs.

I wouldn't have mentioned the different discrepancies as above if I wasn't doing it right. A "dead" product on a continually evolving Mac OS.

[worltraveler]

LibreOffice displays both the Description and the URL in the Cell... about 90% of the Cells, the other 10% are just the Description without the URL... with no space before the "http"...

Data > Text to Columns on "http" makes a real mess in both the source column and the new column.

Find and Replace "http" with " http" does put the space in between.

I'll see what PHP does with the file with PHPExcel.

[Zizi64]

My biggest complaint about Calc is any time I try to type a number >10 (or so) digits, it converts it to Scientific Notation (even though the Cell was formatted as "@ text"... or copy/paste or type digits with '-' or '/' and it keeps converting to "date" or a different variation of "date," or the Epoch number for the date in question. Just a pain. ISBN-13 numbers are not Scientific Notation.

Format the target cells as TEXT (by an user defined CELL STYLE) - BEFORE you enter any numeric-like content into them.
Then the "long numbers" wil not be recognized as numbers and they will not be converted into scientific format.

Otherwise the full format of the ISBN numbers never will be recognized as number, because it contains letters an a space:
ISBN 978-0-306-40615-7
If you use the numbers only (9780306406157), then you must preformat the cell (by Styles), or you can use an apostrophe sign at beginning of the "number":
'9780306406157
The apostrophe sign will not be appeared, but it will control the AOO CALC software: "this is a text, not needed convert it".

[worltraveler]

Which I do, which I shouldn't have to do, because raw data should never be manipulated.

[worltraveler]

In the end, the whole thing failed, because the next day I opened my OO Calc file described above, and neither of the two fields, the one with the hyperlink and the one with the =CELL_URL(), displayed the same as the night before, before saving.

I tried saving as .xls and .ods. And re-created from scratch in between tries.

So far, LibreOffice hasn't liked the =CELL_URL() function at all.

[Villeroy]

This is a spreadsheet program, an arithmetic tool. All spreadsheet programs depend on their ability to store numbers unless it is not a number at all or unless the user insists on a number being text. This is how they all use work since Visicalc of 1979. Unfortunately, only a tiny fraction of todays users has a faint idea about the capabilities and the intended use of a spreadsheet. Most of todays users dump database data into it, complaining that this tool does not behave like a database. It has no tables unless you treat a rectangle of cells as a table. It has no fields of predefined data types unless you handle a column of cells as if it were a field of a certain data type. It has no records unless you treat a row of cells in a consistent manner. It has no relations until you implement relations by means of error prone lookup formulas. One formula returns a single value (with the exception of array formulas) while many users expect formulas to return tables like database queries do. The user interface is highly versatile. It does not allow any "fool proof" solutions, no matter how many form controls and macro code you attach to it.

[Lupp]

Which [format cell 'Text' or use apostrophe as input marker] I do, which I shouldn't have to do, because raw data should never be manipulated.

You are welcome!

This apodictic posture would also require to keep as text e.g.: 123; 123.45; 0.314E1; 10:11:45, .... In consequence it would require to make every refernce asking for a numeric return explicit with this aspect. =SUM(A2:B15) with some cells in A2:B15 then at best containing text-constants to be interpreted as numeric would no longer work. What about =SUM(IF(IS_MEANT_AS_NUMBER(A2:B15);CONVERT_TO_NUMBER(A2:B15);"")) ? You surely will suggest an implementation of the new functions. If every typed-in number is kept as text you have no means to express the difference except by a full-grown mandatory typing system.
If you need to have that you have to program your thing with a full-grown programming system (a real one!) not containing any automatic conversions.
Implementing the attitude in spreadsheets would reduce their usability in the real world by about 100%.

The current handling of the issue is not really happy, imo. It is applicable, however.