# Convert unicode to corresponding text in Excel spreadsheet

I use "Char" to convert an ascii number to a character. What do I use to convert a unicode number to a character?

I'm building a Hebrew conversion chart. For example, 1489 is the letter "BET". I have 1489 in column A, and want to show a BET in column B.

-

## 2 Answers

I think you have to do your own function, VBA has a function to get the character from a code, but I don't know a worksheet function. So, just put in a module of your Excel file this function:

``````Function Unicode(val As Long)
Unicode = ChrW(val)
End Function
``````

And then you can call this from your worksheet using `=Unicode(A1)`

If your Excel has a font that is able to display your character then you will see it.

(if your numbers are in hex):

``````Function Unicode(val As String)
Unicode = ChrW("&H" & val)
End Function
``````
-
Courier New or Arial Unicode MS should work. –  Ellesa Jul 13 '11 at 3:58

I ran into this little problem today, so here's an update:

Excel 2013 introduced the function UNICHAR() which returns the character of the Unicode value given in decimal notation. The function UNICODE() works the other way around, returning a character's Unicode value. This is not available in earlier Excel versions, including Office for Mac 2011.

The exact same functions are however available in the current version of the open-source software LibreOffice Calc (and OpenOffice Calc, I suppose).

E.g., you have a long column 'A' with hexadecimal Unicode values (without the “U+”) and want the characters in column 'B'. Since UNICHAR needs decimal values, combine it with the conversion function HEX2DEC() in the formula. So, in cell B1 put in the formula:

``````=UNICHAR (HEX2DEC (\$A1))
``````

Or, if the values are already in decimal format:

``````=UNICHAR (\$A1)
``````

To apply the formula to all cells below B1, double-click the little handle on the lower left corner of the highlighted cell frame (works in Calc and Excel). Format the column with a font that covers as many Unicode glyphs as possible, e.g., MS Arial Unicode (not the 'normal' Arial). That's it.

(A problem might occur, where the cells display the formula itself instead of the results. In this case the column needs to be formatted to the content type 'text' or 'general'. )

PS:

In Apple Numbers ’09, CHAR () does work with Unicode values. The formula is:

``````=CHAR (HEX2DEC(A))
``````

or

``````=CHAR (A)
``````
-