How to Reorder and Alphabetize Text Lists Inside Excel Cells
Question details
The user needs to sort comma-separated text items contained within a single Excel cell alphabetically and output the reordered list as a single text string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing unorganized comma-separated text data located inside individual cells without splitting them permanently into multiple columns.
- Observed behavior
- The text items remain unsorted in a single string, and the user wants a dynamically reordered string separated by commas.
Ensure you are using a version of Excel that supports dynamic array functions (like Microsoft 365) if you plan to use the formula method. For older versions, the Power Query method is required.
Use Dynamic Array Formulas (TEXTSPLIT, SORT, TEXTJOIN)
Best for users on Microsoft 365 or newer versions that support dynamic arrays.
This method splits the text into an array, sorts it alphabetically, and joins it back together into a single cell.
Click on a blank cell adjacent to the cell containing your comma-separated list (e.g., cell B2 next to A2).
Type the following formula: =TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A2,", "),,,TRUE))
Press Enter. The formula separates the items using TEXTSPLIT, alphabetizes them with SORT across columns, and recombines them using TEXTJOIN. Drag the fill handle down to apply it to other rows.
Use Power Query for Batch Processing
Ideal for large datasets or older versions of Excel that do not support dynamic array functions.
Sort Text Within Cells Easily in WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced array functions including TEXTJOIN, SORT, and TEXTSPLIT, allowing you to instantly reorganize string data within your cells for free.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the dataset containing your unsorted cell values.
- 2. Select the target cell: Click on the empty cell where you want the alphabetically sorted list to appear.
- 3. Input the array formula: Type the formula =TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A2,", "),,,TRUE)) and press Enter.
- 4. Apply to multiple cells: Click and hold the bottom-right corner of the cell, then drag the fill handle down to apply the sorting logic to the rest of your list.
Frequently Asked Questions
Why is the TEXTSPLIT function returning a #NAME? error?
The #NAME? error occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support the TEXTSPLIT function. You will need to upgrade to Microsoft 365, Excel 2021, or use the Power Query method instead.
How do I sort the items in descending alphabetical order?
To sort in descending order, you must change the sort_order argument within the SORT function. Modify your formula to look like this: =TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A2,", "),1,-1,TRUE)). The '-1' tells Excel to sort Z to A.
Can I use a different delimiter besides a comma and a space?
Yes, you can specify any delimiter you need. Simply change the ", " string in both the TEXTSPLIT and TEXTJOIN parameters of the formula to your desired character, such as a semicolon (";") or a pipe symbol ("|").
