I have a spreadsheet with hexadecimal Unicode code-points in one column. Usually it’s just a single number, which I can convert to a character with
=CHAR(HEX2DEC(XY123)). In some cases, though, there are multiple code-points separated by spaces, e.g.
1F449 1F44C, which I can split into an array and rejoin with
=JOIN(; SPLIT(XY123; " ")) or
=CONCATENATE(SPLIT(XY123; " ")).
How can I convert each of the numbers into a character before concatenating them (like in a
for each … in … loop in many programming languages)?
=CONCATENATE(CHAR(HEX2DEC(SPLIT(XY123; " ")))) results in a
#VALUE! error. I can easily get a string of numeric xML character references by using
="&#x" & JOIN(";&#x"; SPLIT(XY123; " ")) & ";", but there seems to be no predefined function to convert these and neither for other escape notation like
="\u" & JOIN("\u"; SPLIT(Codepoint!F8; " ")).
PS: I know it should be pretty simple with a Custom Function in Google Sheets, but I’m hoping for a more generic solution.