Thanks for the clarification. My personal preference is to do this in two fields, just to keep the formulas somewhat manageable and to reduce redundant code. First, the formula that extracts the text. I named this field {Extracted}
, and assumed the URL is in a similarly-named field:
IF(URL, TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100)))
Then the formula that re-builds the URL using that extracted piece:
IF(URL, LEFT(URL, FIND('">', URL) - 1) & ENCODE_URL_COMPONENT(Extracted) & '">' & Extracted & "</a>")
If you would prefer to have everything in a single field, here’s the mashup of those two into a single mega-formula:
IF(URL, LEFT(URL, FIND('">', URL) - 1) & ENCODE_URL_COMPONENT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100))) & '">' & TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(URL, "</a>", ""), ">", REPT(" ", 100)), 100)) & "</a>")