VBA & Macro Problems

Alphabetize Words Within Multiple Excel Cells

Maira MehtabMaira Mehtab Nov 20, 2026 868 views

Question details

Alphabetize words or phrases within more than 2,500 Excel cells while keeping each sorted list in its original cell.

How to Alphabetize Words Within Multiple Excel Cells
Product
Microsoft Excel
Device & OS
not provided
Scenario
Cells contain lists separated by commas, line breaks, or both, and each cell needs to be sorted independently.
Observed behavior
Each cell should retain its own entries in alphabetical order without moving them into other cells.
Before you start

Use a desktop version of Excel that supports VBA, and check that commas and line breaks separate entries rather than form part of a phrase. The macro below replaces mixed separators with line breaks and skips formulas.

Solution 1Recommended

Sort Each Selected Cell with a VBA Macro

Use this method to sort many cells independently when a consistent line-break separator is acceptable.

The macro treats each comma or line break as an entry separator, trims surrounding spaces, removes empty entries, and sorts without regard to letter case. It skips formulas, errors, and non-text values.

1
Open the VBA editor

In desktop Excel, open the workbook and press Alt+F11 on Windows. In the VBA editor, choose Insert > Module.

2
Insert the macro

Paste this code into the module: Sub SortWords() Dim cell As Range, parts As Variant, items() As String Dim text As String, temp As String Dim i As Long, j As Long, n As Long For Each cell In Selection.Cells If Not cell.HasFormula Then If Not IsError(cell.Value2) Then If VarType(cell.Value2) = vbString Then text = Replace(cell.Value2, vbCrLf, vbLf) text = Replace(text, vbCr, vbLf) text = Replace(text, ",", vbLf) parts = Split(text, vbLf) ReDim items(0 To UBound(parts)) n = 0 For i = LBound(parts) To UBound(parts) temp = Trim(parts(i)) If Len(temp) > 0 Then items(n) = temp n = n + 1 End If Next i For i = 0 To n - 2 For j = i + 1 To n - 1 If StrComp(items(i), items(j), vbTextCompare) > 0 Then temp = items(i) items(i) = items(j) items(j) = temp End If Next j Next i If n > 0 Then ReDim Preserve items(0 To n - 1) cell.Value2 = Join(items, vbLf) cell.WrapText = True End If End If End If End If Next cell End Sub

3
Test the macro

Return to the worksheet and select a small range of text cells. Press Alt+F8 on Windows, select SortWords, and click Run. Check the sorted entries and line breaks.

4
Sort the remaining cells

Select the full target range, then use Alt+F8 > SortWords > Run. If you want to retain the macro in the workbook, use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm).

Sort Each Selected Cell with a VBA Macro
Check the results on a copy: Excel cannot normally undo a macro. Work on a copy and test first, especially if entries contain commas as part of a phrase or begin with an equals sign. The macro does not preserve the original mix of separators.
Free Microsoft Office alternative

Work with Excel Lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move existing workbooks. The Excel VBA macro in this article is intended for Microsoft Excel; WPS macro support can vary by edition.

Open and edit lists stored in spreadsheet cellsCompatible with Microsoft Excel formats such as XLSX and XLSFamiliar spreadsheet interface for a smooth transitionFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Will the macro keep each list in its original cell?

Yes. Select the target range and run SortWords from the macro dialog. The macro sorts each eligible cell separately and writes the result back to that cell.

Can I keep both commas and line breaks as separators?

Not with the macro shown here. It treats both as separators and joins the sorted entries with line breaks. Preserving the original mixture requires a different approach.

Why were some selected cells not changed?

The macro skips cells containing formulas, errors, or non-text values. Check the cells in the formula bar and select the text cells you want to sort.

Can Power Query sort the entries instead?

Yes. You can load the data through Excel's Data tab, split entries into rows, sort them, and group them back by their original cell identifier. Mixed commas and line breaks may need to be normalized first, so their original pattern may not be preserved.