I want to substitute
“First” for “1st”,
“Second” for “2nd”,
etc.
I can do this with nested formulas:
=REGEX(REGEX(REGEX(cell,"1st","First"),"2nd","Second"),"3rd","Third")
What I’m looking for (for more complex cases) is sth like this:
*FORMULA("1st"|"First","2nd"|"Second","3rd"|"Third")
Does this exist?
in plain Calc, need to first match,
then lookup,
then replace
which should again question the size of this rabbit hole : Can I use a cell to form part (only) of a regex? - #9 by fpy 
so, probably worth exploring other tools, like python - Ordinal numbers replacement - Stack Overflow
I built this! (Learning faster than I thought possible 
)
Uses a lookup table:
| Items to replace | Replacement for each item |
|---|---|
| 1st | First |
| 2nd | Second |
= REGEX(cell,"("&TEXTJOIN("|",1,itemsToReplace)&")", # Find items to replace # VLOOKUP(REGEX(cell,"("&TEXTJOIN("|",1,itemsToReplace)&")"), # Feed found item into lookup # replacementTable,2,0)) # Replace from second column #
Any feedback welcome - I sense there should be a more efficient way of giving the Expression element of the REGEX.
Also - and this is important for my use case, i.e., cleaning punctuation out - I’m getting unhelpful results when I search for ( , ) , or ' ← should be serifed apostrophe, not straight.
And so far I haven’t tried a global regex - that might break it, though I think not unless there is more than one item found. So Southend-on-Sea will be ok, but 1st Floor 4-6 High Street will not?
@PKG
How can i set your setting to another language cause my origin is deutsch and so the transform after adapting isn’t longer correct?!
Just make sure that number format uses the correct locale. In the file attached by @PKG, the number format of the formula cells are already using “default” locale, and Ctrl+Shift+F9 recalculates it to e.g. my native language.
