How to Convert Escaped Unicode to UTF-8 Characters in Excel VBA
Question details
The user needs a method to convert escaped Unicode sequences returned by an API into readable UTF-8 text using Excel VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Handling JSON strings from an API response that contain escaped Unicode characters, such as Hebrew text formatted as \u05de.
- Observed behavior
- Excel VBA reads the escaped Unicode strings literally (e.g., \u05de) instead of automatically decoding and displaying the correct UTF-8 text.
Ensure that the Developer tab is enabled in your Excel ribbon and that you have basic familiarity with writing and running functions in the VBA Editor.
Use a Custom VBA Function with ChrW to Decode the String
Create a custom VBA function that loops through the string, extracts the 4-digit hexadecimal code after each \u prefix, and converts it using the ChrW function.
Since VBA does not natively parse \uXXXX JSON sequences in strings, you must manually isolate each hexadecimal code. By replacing the \u prefix with VBA's hex identifier (&H) and passing it to the ChrW function, you can successfully rebuild the decoded string.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
Click 'Insert' in the top menu and select 'Module' to create a blank script window.
Write a custom function that uses the Split function to divide the text at the backslash (\). Loop through the resulting array, checking if the string starts with 'u'. If it does, extract the next 4 characters and pass them through ChrW("&H" & hexValue).
Concatenate the newly converted characters with the remaining standard text in the string. Use this custom function in your API response processing script to cleanly format the UTF-8 output.
Handle Macros and Data Effectively with WPS Spreadsheet
WPS Spreadsheet offers a built-in macro editor with excellent compatibility for VBA string manipulation functions, allowing you to easily process API data and convert Unicode sequences.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your raw API data.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Macro Editor' or press Alt + F11.
- 3. Insert Your VBA Code: Go to 'Insert' > 'Module' and paste your custom ChrW Unicode conversion function into the editor.
- 4. Run the Conversion: Execute the macro to automatically parse and replace all \uXXXX sequences with correct UTF-8 characters across your dataset.
Frequently Asked Questions
Why does Excel VBA show \uXXXX instead of actual characters?
When fetching data from web APIs, non-ASCII characters are often returned in a JSON response as escaped Unicode (e.g., \u05de). Excel VBA reads string literals as-is and does not automatically decode JSON Unicode sequences unless explicitly instructed via a custom parsing script.
What does the ChrW function do in Excel VBA?
ChrW is a built-in VBA function that returns a Unicode character corresponding to a specified character code. It is essential for converting hexadecimal values into characters outside the standard ASCII range, such as Hebrew, Arabic, or Asian characters.
Can I use standard Excel formulas to decode escaped Unicode without VBA?
Standard Excel functions do not offer a direct way to decode \uXXXX sequences. However, you can use Excel's Power Query feature to import and parse the JSON response natively, which will automatically handle and display the escaped Unicode characters properly.
