I have cells containing text along with escaped unicode char, like : sue\u00f1os
I would like using a formula to decode the unicode char and obtain : sueños
I looked at the UNICODE or UNICHAR functions, but they are not matching what I require.
1 Answers
With a your example in cell A1, then in legacy Excel
=LEFT(A1,FIND("\u",A1)-1)&UNICHAR(HEX2DEC(MID(A1,FIND("\u",A1)+2,4)))&RIGHT(A1,LEN(A1)-FIND("\u",A1)-5)
or, in dynamic-array Excel,
=LET(pos,FIND("\u",A1),LEFT(A1,pos-1)&UNICHAR(HEX2DEC(MID(A1,pos+2,4)))&RIGHT(A1,LEN(A1)-pos-5))
but I have to agree about using Find & Replace, especially if you only have a few distinct UNICODE characters to replace - unlike with VBA, in being able to iterate through the whole workbook, this is one UI feature that is more efficient than when effected through code.
Related