LibreOffice Basic Function to Format URLs of Cell

Estimated reading time of this article: 2 minutes

I have URLs in a cell of LibreOffice that I want to output as Markdown links.

https://de.wikipedia.org/wiki/Jan_Palach
→
[wikipedia.org](https://de.wikipedia.org/wiki/Jan_Palach)

Example

In LibreOffice, I have several links in a cell. The links are separated by a new line (Ctrl-Enter or chr(10)).

Cell content:

http://www.breitbart.com/tech/2015/12/30/software-pioneer-ian-murdock-dead-after-extraordinary-police-brutality-claims/
http://arstechnica.com/information-technology/2015/12/ian-murdock-father-of-debian-dead-at-42/
http://www.linux-magazin.de/NEWS/Debian-Gruender-Ian-Murdock-ist-tot

Desired output:

[breitbart.com](http://www.breitbart.com/tech/2015/12/30/software-pioneer-ian-murdock-dead-after-extraordinary-police-brutality-claims/)
[arstechnica.com](http://arstechnica.com/information-technology/2015/12/ian-murdock-father-of-debian-dead-at-42/)
[linux-magazin.de](http://www.linux-magazin.de/NEWS/Debian-Gruender-Ian-Murdock-ist-tot)

Basic Function

The link text should consist of the domain name and it should point to the given URL.

Sadly, LibreOffice Basic does not have any regex support. The pattern matching and string substitution must be achieved by existing Basic string functions.

Function FormatURLsToMD(str as String) as String
    Dim md, url, domain as String
    Dim urls(), subdomains() as String
    Dim nUrl, pos as Integer
    nUrl = 0
    md = ""

    urls = split(str, chr(10))
    For Each url in urls
            domain = trim(url)
            if len(domain) > 4 then
                domain = replace(domain, "http://", "")
                domain = replace(domain, "https://", "")
                pos = inStr(domain, "/")
                IF pos > 0 THEN
                    domain = Left(domain, pos - 1)
                END If
                subdomains = split(domain, ".")
                if ubound(subdomains) > 0 then
                    Rem last 2 domains
                    domain = subdomains(ubound(subdomains) - 1) & "." & subdomains(ubound(subdomains))
                else
                    domain = ""
                endif
                md = md + " [" & domain & "](" & url & ")"
            else
                md = md + ""
            end if
    Next url
    FormatURLsToMD = md
End Function

Function replace(source as String, searchStr as String, replaceStr as String) as String
    replace = join(split(source, searchStr), replaceStr)
End Function

This function can be called in a cell by =FormatURLsToMD(A1) where A1 is a cell reference or =FormatURLsToMD("http://www.linux-magazin.de/NEWS/Debian-Gruender-Ian-Murdock-ist-tot").

I am not an LibreOffice Basic expert. This is my solution to my problem. If you know a more elegant solution, please let me know.

JavaScript/Python alternative

LibreOffice can execute JavaScript and Python scripts. However, such functions cannot be called directly from a spreadsheet. Thus, a Basic function wrapper must be created. This solution could be preferable for more complex functions to circumvent the Basic language limitations.

(aktualisiert am ) –