Workarounds for Excel Custom List Limits with Unicode and CJK Characters
Question details
The user needs to understand why Excel fails to store a complete custom sorting list containing thousands of Chinese, CJK, or other Unicode characters, and how to successfully sort the data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to create a very large custom sorting list using thousands of Unicode or CJK characters to sort worksheet data.
- Observed behavior
- Excel truncates the custom list, storing only part of the entries because the list exceeds the maximum allowed character or size limits for delimited strings.
Verify the total character count of the data you want to sort by; if it approaches or exceeds 20,000 characters, you will need to use a formula-based workaround rather than the built-in Custom List feature.
Use a Helper Column and Lookup Table
Bypass custom list size limits by creating a lookup table that assigns a numeric sort order to your CJK or Unicode characters.
Excel stores custom lists as delimited text strings. When a list contains tens of thousands of characters, it exceeds the internal size limitations and gets truncated. A lookup table sidesteps this completely by relying on standard cell references, which can handle millions of rows.
On a new worksheet, create a two-column table. In the first column, paste your complete list of Unicode or CJK characters. In the second column, assign a sequential number (1, 2, 3...) representing their desired sort order.
Go back to your main data sheet. Insert a new, empty column next to the data you want to sort. Name it 'Sort Rank'.
In the first cell of the helper column, enter a VLOOKUP or XLOOKUP formula to find the character in your reference table and return its corresponding numeric rank. Drag the fill handle down to apply the formula to all rows.
Select your entire dataset, go to the 'Data' tab, and click 'Sort'. Choose your new 'Sort Rank' column as the 'Sort by' criteria and select 'Smallest to Largest'.
Define the Sort Order Using VBA
Use a VBA macro to define the sorting array programmatically, which is useful for advanced users needing repeatable sorting without helper columns.
Overcome Sorting Limitations with WPS Office
WPS Spreadsheet provides powerful lookup functions and data handling capabilities, allowing you to easily sort massive datasets of Unicode and CJK characters using helper columns without experiencing truncation or crashes.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your extensive list of Unicode or CJK characters.
- 2. Set Up a Lookup Sheet: Create a new sheet and list your characters alongside their intended numerical rank to serve as a reference table.
- 3. Use XLOOKUP or VLOOKUP: In your main sheet, create a 'Rank' column and use the VLOOKUP function to automatically pull the numerical values from your reference sheet.
- 4. Execute the Sort: Highlight your data, navigate to the 'Data' tab, select 'Sort', and organize your rows based on the numerical 'Rank' column.
Frequently Asked Questions
What is the character limit for an Excel custom list?
Custom lists in Excel are stored as a single delimited text string in the registry or workbook background. If the combined text size of the entries exceeds approximately 20,000 to 25,000 characters, Excel will typically truncate the list and only save the first portion.
Why do Chinese and CJK characters cause custom list issues so quickly?
CJK characters are Unicode. While standard characters might consume fewer bytes, large sets of complex Unicode characters consume significant memory when stored in a delimited string format, causing them to hit Excel's backend size limits much faster than standard alphanumeric lists.
Can I import a custom list from a cell range to bypass the limit?
No. Even when you select 'Import list from cells' in the Custom Lists dialog box, Excel still converts that range into a delimited text string for storage. The same overall character and size limitations will apply, and the list will still be truncated.
Are there limits to using a lookup table instead of a custom list?
A lookup table is essentially only limited by the spreadsheet's maximum row count (1,048,576 rows). This makes it exponentially more scalable and reliable for large custom sorting tasks compared to the built-in Custom List feature.
