Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

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)

Result of the user defined function

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
share|improve this answer
    
Courier New or Arial Unicode MS should work. –  Ellesa Jul 13 '11 at 3:58
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.