Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining an ANSI Value in a Macro.
When creating a macro, you can use the Asc function to determine the ANSI value of the first letter of a string. In early versions of BASIC, Asc returned the ASCII value, but Excel uses only ANSI values. The function uses the following format:
x = Asc(y)
where x is the variable that the ANSI value should be assigned to, and y is the string to be analyzed. The way in which the Asc function works is very similar to the CODE worksheet function.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9202) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Determining an ANSI Value in a Macro.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel Data Analysis and Business Modeling today!
Macros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. ...
Discover MoreVBA makes it easy to copy a worksheet from the current workbook into a brand-new workbook. You may want to delete some ...
Discover MoreYou may need to automatically generate unique numbers when you create new worksheets in a workbook. Here are a couple of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-08-20 06:23:35
Mike J
@J.Woolley
Thank you, but I confess Gemini did all the work - even carrying on to generate the code to produce all the emojis available in a spreadsheet, with their IDs, for direct use in a macro.
I have used AI a few time and been surprised that none of ChatGPT, Claude or Gemini credit themselves in their code.
2026-08-19 16:46:41
J. Woolley
@Mike J
Mea culpa. Your code is correct; mine is wrong.
Here is your code formatted for looks:
Function EXCEL_UNICODE(txt As String) As Long
Dim highSurrogate As Long, lowSurrogate As Long
If Len(txt) = 0 Then Exit Function
'Get the first 16-bit block
highSurrogate = AscW(Mid(txt, 1, 1))
If highSurrogate < 0 Then highSurrogate = highSurrogate + 65536
'Check if it's the start of an emoji/surrogate pair (&HD800 to &HDBFF)
If highSurrogate >= 55296 And highSurrogate <= 56319 _
And Len(txt) > 1 Then lowSurrogate = AscW(Mid(txt, 2, 1))
If lowSurrogate < 0 Then
lowSurrogate = lowSurrogate + 65536
'Recombine the pair into the true 32-bit Unicode Code Point
EXCEL_UNICODE = (highSurrogate - 55296) * 1024 _
+ (lowSurrogate - 56320) + 65536
Else
EXCEL_UNICODE = highSurrogate
End If
End Function
Function EXCEL_UNICHAR(code As Long) As String
Dim highSurrogate As Long, lowSurrogate As Long
'If it's a true 32-bit character (like an emoji)
If code > 65535 Then
code = code - 65536
highSurrogate = (code \ 1024) + 55296
lowSurrogate = (code Mod 1024) + 56320
'Re-encode back into VBA's expected signed format
If highSurrogate > 32767 Then highSurrogate = highSurrogate - 65536
If lowSurrogate > 32767 Then lowSurrogate = lowSurrogate - 65536
EXCEL_UNICHAR = ChrW(highSurrogate) & ChrW(lowSurrogate)
Else
'Standard or 16-bit character
If code > 32767 Then
EXCEL_UNICHAR = ChrW(code - 65536)
Else
EXCEL_UNICHAR = ChrW(code)
End If
End If
End Function
2026-08-19 04:57:19
Mike J
@J.Woolley
Thanks, I have just tried Unicode() and Unichar() in Excel 2021 and, as you rightly say, they perform correctly.
But =AscW(text) was exactly what AI came up with at its first attempt and it didn't work. I got -10179 as a result.
Its second attempt simply subtracted 65536 if the number was negative and that didn't work either. I got 55357 as result.
Its final version, the one in my comment, produced the correct result of 128221, which is an emoji of a pad and pen.
Perhaps more interesting is that =AscW(Text) doesn't work in Excel 2021 VBA either, so presumably one would have to rely on
Application.WorksheetFunction.UNICODE() or something like AI produced if you want to incorporate it inside a macro for extended unicode characters.
2026-08-18 12:05:54
J. Woolley
@Mike J
I guess you're stuck with Excel 2010 so you don't have Excel 2013 functions UNICODE and UNICHAR, which correspond to VBA AscW and ChrW. Maybe you should consider upgrading.
Your chat with AI must have been confusing. Here's some code to try instead:
Function MyUNICODE(text As String) As Long
MyUNICODE = AscW(text)
End Function
Function MyUNICHAR(val As Long) As String
MyUNICHAR = ChrW(val)
End Function
2026-08-17 13:47:59
Mike J
I recently had a chat with AI trying to find the code of a particular character I downloaded as part of a data block.
It gave me the code and char versions each time.
the first versions used AscW() and returned a 5-digit negative code, but it would not reverse correctly
The second version also used Ascw(), but added 65536 if it was negative and returned a 5 digit positive code, but that still did not reverse correctly
The final version correctly returned a 6 digit positive code, and reversed correctly.
The codes, which work in Excel 2010, are below:
Function EXCEL_UNICODE(txt As String) As Long
Dim highSurrogate As Long, lowSurrogate As Long
If Len(txt) = 0 Then Exit Function
' Get the first 16-bit block
highSurrogate = AscW(Mid(txt, 1, 1))
If highSurrogate < 0 Then highSurrogate = highSurrogate + 65536
' Check if it's the start of an emoji / surrogate pair (&HD800 to &HDBFF)
If highSurrogate >= 55296 And highSurrogate <= 56319 And Len(txt) > 1 Then
lowSurrogate = AscW(Mid(txt, 2, 1))
If lowSurrogate < 0 Then lowSurrogate = lowSurrogate + 65536
' Recombine the pair into the true 32-bit Unicode Code Point
EXCEL_UNICODE = (highSurrogate - 55296) * 1024 + (lowSurrogate - 56320) + 65536
Else
EXCEL_UNICODE = highSurrogate
End If
End Function
Function EXCEL_UNICHAR(code As Long) As String
Dim highSurrogate As Long, lowSurrogate As Long
' If it's a true 32-bit character (like an emoji)
If code > 65535 Then
code = code - 65536
highSurrogate = (code \ 1024) + 55296
lowSurrogate = (code Mod 1024) + 56320
' Re-encode back into VBA's expected signed format
If highSurrogate > 32767 Then highSurrogate = highSurrogate - 65536
If lowSurrogate > 32767 Then lowSurrogate = lowSurrogate - 65536
EXCEL_UNICHAR = ChrW(highSurrogate) & ChrW(lowSurrogate)
Else
' Standard or 16-bit character
If code > 32767 Then
EXCEL_UNICHAR = ChrW(code - 65536)
Else
EXCEL_UNICHAR = ChrW(code)
End If
End If
End Function
I haven't done extensive testing on this code, but it still seems to work with simple ascii characters
2026-08-15 15:41:54
J. Woolley
ASCII character codes range from 0 to 127 (7-bits).
ANSI character codes range from 0 to 255 (8-bits).
If you might have UNICODE characters beyond ANSI like ✓ (check) or Δ (delta), use AscW(...) instead of Asc(...). Both return the same value for ASCII or ANSI characters, but Asc(...) returns 63 (?) for UNICODE characters beyond ANSI.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments