Macro to turn excerpts of text into links with a predetermined format

[pablobm]

I have a spreadsheet with a number of columns, one of which holds free-form text. In these pieces of text, there are excerpts that I’d like to turn into links, following a specific scheme.

The text to turn into links looks like #123, this is: a pound/hash symbol followed by an integer number. I would like to turn each of these instances into a link such as #123.

For example, if I have this text (all in a single cell, there are newlines):

Attended meeting
Created ticket #321

I would like it turned into something like this:

Attended meeting
Created ticket #321

(This is my first post and I’m not allowed to include more links, but there could be more lines in the example above with more instances of the piece of text to transform).

Currently I am doing this manually but it’s a bit labour intensive. I’ve looked a bit into macros but I can’t quite figure it out. I’m not even sure I’m finding the right documentation or examples. I have also tried with macro recording, but it doesn’t seem to support this use case.

How could I automate this process (presumably with a macro)?

In case it helps when figuring out how much detail to give in the answer: I am a confident coder, just new to LibreOffice macros.

[Villeroy]

You are working on a spreadsheet. Just use it.
=HYPERLINK("https://example.org/"&MID(A1;2;LEN(A1)-1);A1)
A bit more sophisticated:
ask135439.ods (43.8 KB)

[pablobm]

Thank you for taking the time to answer. I considered using HYPERLINK, but what I need is slightly more specific. The text I show as example would be all in a single cell, and only a small portion of the text would be a link, not the whole cell. Also cells can have newlines, and there might be more than one piece of text to link in each. Here’s a screenshot:

Hence my assumption that I may need a macro. This macro would visit cells, look for the linkable substrings (text that looks like hash-number), convert each instance, and move on to the next cell.

[Villeroy]

Are the blue portions URLs? Why don’t you upload a sample document? Screenshots are a waste of time, unless we have to deal with display issues.

[pablobm]

Thank you. Yeah, I wasn’t sure of the etiquette here, or if I was going to be allowed (as I’m a new user). Attaching: sheet-with-long-text-with-links.ods (14.9 KB)

To answer your immediate question: yes, the blue portions are links.

[fpy]

@pablobm

etiquette

Ask/Guide - How to use the Ask site - The Document Foundation Wiki

[Villeroy]

hyperlink_UDF.ods (24.3 KB)
Quick, off the shelf user defined function extracting the URLs.
=CELL_URL(sheet;row;column;[n]) takes 3 positional arguments to specify the cell by indices. The optional 4th argument is the index of the URL to be extracted (default 1).

[sokol92]

@pablobm

Hence my assumption that I may need a macro. This macro would visit cells, look for the linkable substrings (text that looks like hash-number), convert each instance, and move on to the next cell.

Will it be sufficient for the macro to process selected cells (you can select an entire sheet if desired)?
The macro text will likely not be short, as it will be necessary to sequentially form the cell text from paragraphs and text fragments.
I’ll also note that Excel does not support hyperlinks within cell text (in case you plan to save your file in Excel format).

[pablobm]

Thank you again @Villeroy. Maybe I should have been more specific. The example I shared wasn’t supposed to be the original document, but rather the end result after applying the macro. I’ll share a new example: sheet-with-long-text-with-links.ods (14.7 KB) This file includes two sheets (tabs at the bottom):

• original: the sheet as I would find it before converting anything.
• result: the sheet as I would like it to be after converting those pieces of text to links.

[pablobm]

@sokol92: what you describe would be sufficient. Also fortunately I am not troubled by the Excel support, so no problem with that.

[sokol92]

Please test the macro in the attached document.

1. Select the cells to replace text fragments with hyperlinks (one or more rectangular cell ranges).
2. Run the SheetTextLib / Module1 / TextFragmentsToLinks macro

If successful, you can move the macro library SheetTextLib to My Macros... and assign a convenient hotkey.

sheet-with-long-text-with-links2.ods (15.8 KB)

[pablobm]

That appears to do the trick! Thank you @sokol92

Out of curiosity: are Text_Insert and Text_PutField subroutines that you normally have in your library? Or did you write them specifically for your answer here?

[sokol92]

@pablobm

Out of curiosity: are Text_Insert and Text_PutField subroutines that you normally have in your library? Or did you write them specifically for your answer here?

I wrote these procedures several years ago to enable the ability to write text with internal formatting into cells and print page headers / footers.
The markup symbols are practically the same as those used by Microsoft.