This article explains the basic formulas and methods for sorting text alphabetically in spreadsheet applications like Microsoft Excel and Google Sheets. You will learn how to use the SORT function, the COUNTIF-based ranking approach, and built-in tools to organize text data, with clear examples and common pitfalls to avoid.
Formula
The core formula for sorting a comma-separated list of text values in Excel is:
=TEXTJOIN(", ", TRUE, SORT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)), (SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)-1)*100+1, 100))))
Where:
- A1 – the cell containing the comma-separated text list (e.g., “banana,apple,cherry”).
- SUBSTITUTE(A1,”,”,REPT(” “,100)) – replaces each comma with 100 spaces, creating a wide delimiter.
- MID(…, (SEQUENCE(n)-1)*100+1, 100) – extracts each item by stepping through the string in 100-character chunks.
- TRIM(…) – removes the extra spaces around each extracted item.
- SORT(…) – sorts the resulting array alphabetically (A to Z by default).
- TEXTJOIN(“, “, TRUE, …) – joins the sorted items back into a single comma-separated string, ignoring empty values.
The variable n is the number of items, calculated as LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1 (the count of commas plus one).
Worked Example
Using the default input from the calculator block: apple,banana,cherry,date (A1).
- Count items: there are 3 commas, so n = 3 + 1 = 4.
- Replace commas with 100 spaces: “apple banana cherry date” (spaces truncated for readability).
- Extract each 100-character chunk: “apple”, “banana”, “cherry”, “date” (after TRIM).
- Sort alphabetically: {“apple”, “banana”, “cherry”, “date”}.
- Join with “, “: “apple, banana, cherry, date”.
The result is the same as the calculator output: apple, banana, cherry, date.
When to Use a Formula vs. Built-In Sort
Use the SORT formula when you need a dynamic, automatically updating sorted list that changes when the source data changes. This is ideal for dashboards, reports, or any scenario where the input list is frequently edited. Use Excel’s built-in Sort feature (Data tab > Sort) when you only need a one-time arrangement and don’t mind modifying the original data order. The built-in sort is simpler and works on entire columns or ranges, but it is static.
Sorting a Column of Text (Not a Single Cell)
If your text items are already in separate cells (e.g., A1:A10), the formula is much simpler:
=SORT(A1:A10)
This returns a dynamic array of the sorted values. To sort in reverse order, use =SORT(A1:A10, 1, -1) where 1 is the column index and -1 means descending. In Google Sheets, the same formula works. For a comma-separated list, you can also use =TEXTJOIN(", ", TRUE, SORT(TRANSPOSE(SPLIT(A1, ",")))) in Google Sheets, which is more straightforward than the Excel MID approach.
Common Mistakes and How to Avoid Them
- Leading/trailing spaces – Items like ” apple” sort before “banana” because space has a lower ASCII value. Always wrap your extraction in TRIM() to avoid this.
- Case sensitivity – The SORT function is case-insensitive by default in Excel (it treats “Apple” and “apple” as equal). If you need case-sensitive sorting, use the SORTBY function with the EXACT function as a helper, but this is rarely needed for basic text.
- Numbers stored as text – If your list contains numbers, they will sort as text (e.g., 10 comes before 2). Convert to numbers first if you want numeric sorting.
- Empty items – Consecutive commas (e.g., “apple,,cherry”) will create empty items. The TEXTJOIN function with TRUE ignores empty values, but the MID extraction may produce empty strings; ensure your formula handles them gracefully.
Limitations of the Basic Formula
The MID-based extraction formula works only for comma-separated lists within a single cell, and it assumes a consistent delimiter (comma). It will fail if items contain commas themselves (e.g., “Smith, John”). For such cases, use a different delimiter like a semicolon or a pipe symbol. Also, the formula uses a fixed chunk size of 100 characters; if any single item exceeds 100 characters, it will be truncated. Increase the 100 to a larger number (e.g., 200) if you expect longer items. The formula is also limited to a reasonable number of items – extremely long strings may hit Excel’s cell length limit (32,767 characters).
Alternatives: Using Helper Columns and COUNTIF
For older versions of Excel that lack the SORT function (pre-2021 or Excel 2019), you can sort text using a helper column with the COUNTIF function. Here’s how:
- Assume your text is in column A (A1:A10).
- In B1, enter:
=COUNTIF($A$1:$A$10,"<="&A1)and copy down. This gives each item a rank based on alphabetical order. - In C1, enter:
=INDEX($A$1:$A$10, MATCH(ROW()-ROW($C$1)+1, $B$1:$B$10, 0))and copy down to get the sorted list.
This approach is more complex but works in any Excel version. However, it doesn't handle duplicates perfectly; you may need to add a tiebreaker. For most users, the SORT function is the recommended and simplest method.
Frequently Asked Questions
How do I sort text in Excel without using a formula?
Select the column or range containing your text, go to the Data tab, and click the A→Z (sort ascending) or Z→A (sort descending) button. Excel will rearrange the rows based on the selected column. This is a static sort – it changes the data permanently. If you need to keep the original order, copy the data to a new location first.
Can I sort text by the second word or by last name?
Yes, use the SORTBY function. For example, if your text is "First Last" in A1:A10, use =SORTBY(A1:A10, TRIM(MID(SUBSTITUTE(A1:A10," ",REPT(" ",100)),100,100))) to sort by the last word. This extracts the second word and uses it as the sort key. Adjust the extraction logic if your names have different structures.
Why does my sort put uppercase letters before lowercase?
In most spreadsheets, the default sort is case-insensitive, so "Apple" and "apple" are treated as equal. However, if you use a custom sort or a formula that relies on character codes, uppercase letters (ASCII 65-90) come before lowercase (97-122). To force a case-sensitive sort, you can use the SORTBY function with the EXACT function as a secondary key, but this is rarely needed.
How do I sort text in Google Sheets?
Google Sheets has the same SORT function. For a column, use =SORT(A1:A10). For a comma-separated list in one cell, use =TEXTJOIN(", ", TRUE, SORT(TRANSPOSE(SPLIT(A1, ",")))). The SPLIT function breaks the list into an array, TRANSPOSE makes it vertical for SORT, then TEXTJOIN recombines it.
What if my list has numbers mixed with text?
The SORT function will sort numbers and text separately, with numbers first by default. If you want a unified alphabetical sort, convert numbers to text using the TEXT function, e.g., =SORT(TEXT(A1:A10,"0")). Be aware that this changes the data type, so numeric sorting (10 before 2) will no longer work.