How to Add Unicode Combining Characters in Excel Formulas
Question details
The user wants to automatically append a Unicode combining character after every letter, number, space, or punctuation mark in a specific text string using a formula.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Modifying a text string dynamically to include specific Unicode characters without having to type them manually after every single character in the cell.
- Observed behavior
- The goal is to output a joined text string with the combining character correctly applied, while understanding the limitations regarding partial rich-text formatting on formula results.
Ensure you are using a modern spreadsheet version (such as Excel 365 or the latest WPS Spreadsheet) that supports dynamic array functions like SEQUENCE and TEXTJOIN.
Use a Dynamic Array Formula with TEXTJOIN and UNICHAR
Combine TEXTJOIN, MID, SEQUENCE, LEN, and UNICHAR functions to automatically insert the combining character after every character in a string.
This method splits the original text string into an array of individual characters, appends your desired Unicode combining character to each element, and then recombines them into a single continuous string.
Click on an empty cell where you want the new text string with the combining characters to appear.
Type the formula =TEXTJOIN("",TRUE,MID(A1,SEQUENCE(LEN(A1)),1)&UNICHAR(863)) into the formula bar. Replace 'A1' with the cell reference containing your original text. Note that '863' is the decimal value for Unicode character U+035F. Adjust this number if you need a different combining character.
Press Enter. The dynamic array formula will automatically generate the modified string.
Format Partial Text Using Separate Cells
Use separate columns for different text segments if you absolutely need distinct rich-text formatting.
Easily Process Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced text manipulation functions, including dynamic arrays like SEQUENCE and TEXTJOIN, allowing you to easily manage Unicode characters and complex text strings.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your text strings.
- 2. Input the array formula: Select the target cell and enter the TEXTJOIN, MID, and UNICHAR combined formula.
- 3. Execute the function: Press Enter to instantly insert the Unicode combining characters into your text.
Frequently Asked Questions
Can I apply bold or color formatting to only the inserted Unicode characters in a formula cell?
No, spreadsheet programs do not support partial rich-text formatting for formula results. Any formatting applied to the cell will affect the entire resulting text string. If you need distinct formatting, you must copy the formula result, paste it as values, and manually format the characters.
How do I find the correct UNICHAR decimal number for a specific Unicode character?
The UNICHAR function requires a decimal number. You can find this by converting the hex code of the Unicode character (e.g., U+035F) to decimal format using an online converter. Alternatively, if the character is already typed in a cell, you can use the UNICODE(cell_reference) function to find its exact decimal value.
Why does the SEQUENCE function return a #NAME? error?
The SEQUENCE function is a dynamic array formula introduced in newer versions of Excel (like Excel 365) and modern WPS Office versions. If you are using an older spreadsheet version that does not support dynamic arrays, the formula will return a #NAME? error.
