I managed to do it with an extra sheet in excel with some coding.

I created an extra Excel sheet called that I use for pieces of coding. I put it in the end of all my normal sheets that I use and use the naming <[name]> to indicate that it is technical stuff.

I created the following parts of codes in cells in this sheet, as text:

Cell A1: 1. Hyperlink generation formula (just a title for my own to remember what is this for)

Cell A3: < a href=" (without the space between < and a)

Cell A4: ">

Cell A5: < /a> (without the space in between < and /)

In my normal sheets I have the two columns, filled with the data:
1: TITLE
2: URL

In total the following formula now works, by getting this coding text data from the sheet:

='>Lists<'!$A$3&'SEO Pages Posts'!D2&'>Lists<'!B4&'SEO Pages Posts'!C2&'>Lists<'!$B$5

An extra explanation of how this formula works:

It is a construct of 5 input fields, divided by the & symbols in this formula:

coding from the Lists sheet cell A3, locked cell with double $ to lock cell

URL, made variable so you can copy and paste this formula to the next row

- coding from the Lists sheet cell A4, locked cell with double $ to lock cell
- TITLE, made variable so you can copy and paste this formula to the next row
- coding from the Lists sheet cell A5, locked cell with double $ to lock cell

Take a moment to digest all of this, and this should be a solution for you, by just having a working formula BUT with an extra sheet for the coding.

If I do it in another way I got errors with creating "< a href=" " (without spaces), where the formula gots confused with the double " in the end.

Wish you all the best.