7

I use the CHAR() function to convert an extended ASCII value into a character. What do I use to convert a Unicode code point into a character?

I'm building a Hebrew conversion chart. For example, code point 1489 ( U+05D1 ) is the letter "Bet". I have 1489 in column A, and want to show a Bet in column B.

3

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
  • Courier New or Arial Unicode MS should work. – Ellesa Jul 13 '11 at 3:58
  • Hi stema. I know it's been a while (8 years!), but I made an improvement to your function so that it would be able to produce a supplementary character from the code point. I posted the code in my answer here. Thanks for providing this :) +1 – Solomon Rutzky Jun 26 at 23:20
3

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)
2

Just to clarify for anyone needing to create Supplementary Characters:

  1. For Excel 2013 and newer, the UNICHAR() function (as shown in TehMacDawg's answer) handles this just fine. You simply pass in the intended Unicode code point:

    =UNICHAR(128126)
    

    should produce: 👾 ( U+1F47E )

  2. For pre-2013 Excel, the function shown in stema's answer is a good start, but the ChrW VB function can only handle BMP code points (i.e. values 0 - 65,535 / 0xFFFF ). Creating a Supplementary Character is possible, but requires calling the function twice to produce both code units of the Surrogate Pair:

    =CONCATENATE(Unicode(55357), Unicode(56446))
    

    will produce: 👾 ( U+1F47E )

    That is a bit cumbersome, so I adapted stema's code to be the following, which translates the code point / UTF-32 value into the UTF-16 Surrogate Pair

    Function UnicodeFromInt(val As Long)
        If val < 0 Or val > 1114111 Then
            UnicodeFromInt = "ERROR: value must be between 0 and 1114111!!"
            GoTo GetOut
        End If
    
        If val >= 55296 And val <= 57343 Then
            UnicodeFromInt = "ERROR: surrogate code points are not displayable!!"
            GoTo GetOut
        End If
    
    
        If val < 65536 Then
            UnicodeFromInt = ChrW(val)
        Else
            UnicodeFromInt = ChrW(55232 + Int(val / 1024)) & ChrW(56320 + Int(val Mod 1024))
        End If
    
    GetOut:
    End Function
    
    Function UnicodeFromHex(val As String)
        UnicodeFromHex = UnicodeFromInt("&H" & val)
    End Function
    

    If you add all of the code above as a single module, then both of the following function calls:

    =UnicodeFromInt(128126)
    
    =UnicodeFromHex("1F47E")
    

    will produce: 👾 ( U+1F47E )

For more details on these two options, plus Unicode escape sequences for other languages and platforms, please see my post:

Unicode Escape Sequences Across Various Languages and Platforms (including Supplementary Characters)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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