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:
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.