How to sort list of numbers separated by commas in Excel?
Sorting a list of values in ascending or descending order is a very common task in Excel. However, things become less straightforward when those numbers are grouped together in a single cell, separated by commas, like in the screenshot below. Many users find themselves unsure of how to sort numbers inside a single cell since Excel's default sorting tools work on rows or columns, not within cell content. In this article, we’ll explore several practical solutions you can use to efficiently sort numbers which are separated by commas inside cells, saving time and reducing manual work in your Excel tasks.
Sort comma separated values with Text to Columns function
Sort comma separated values with Kutools for Excel
Sort comma separated values with VBA code
Sort comma separated values with Text to Columns function
To sort numbers separated by commas within a single cell, you first need to temporarily split those numbers into separate cells, sort them, and then (if needed) recombine them. Excel’s Text to Columns feature can quickly separate comma-delimited data into individual columns, after which you can transpose, sort, and further process your data. This approach works well if you are comfortable with cell transformations and manual steps.
Scope: This method is best for users who handle small to moderate lists and don’t need to repeat the process often, as it does require several manual steps and may not be ideal for dynamic or frequently updated data.
Some points to consider: Make sure there are enough empty cells to the right of your selected column to avoid overwriting existing data when splitting with Text to Columns. Remember to check your data for any spaces after commas, as those can affect splitting results and require trimming. Recombining sorted values into a single cell is not automatic in this method and would require further steps.
1. Select the cells you want to split by comma, and click Data > Text to Columns. In the step1 of Convert Text to Columns Wizard, choose the Delimited option to specify that commas will be used to split the text. See screenshot:
2. Click Next to proceed to step 2 of the Wizard. Check the Comma delimiter box, then click Next again to reach step 3 of the Wizard. Here, select a cell where you want to output the separated values, making sure the output range has enough space to the right. See screenshot:
3. Click Finish, and the values will be split into adjacent columns. Check that numbers are split as expected; if not, check for leading/trailing spaces in your original data. See screenshot:
4. To prepare for sorting, select all split cells, press Ctrl + C to copy, then right-click on a blank area, choose Paste Special > Transpose. This will arrange the numbers vertically in a single column. See screenshot:
Once transposed, your numbers should look like this:
5. Now, select the column with the transposed values. Go to the Data tab and click your preferred sorting option in the Sort & Filter group (either ascending or descending order). If the Sort Warning dialog appears, select Continue with the current selection and confirm with OK.
The selected column is now sorted in the order you chose. If you have multiple cells to process, repeat this action for each one.
6. Repeat the process above for each set of transposed values. Note that this method is manual and works best for shorter lists.
Tip: After sorting, if you want to rejoin the numbers into a single cell using comma separation, you can use the TEXTJOIN function (in Excel 2016 or later) or concatenate the items with the CONCATENATE operator manually. Watch for common errors like missing values or incorrect separator placement.
Sort comma separated values with Kutools for Excel
When working with larger datasets or performing this kind of task regularly, using tools like Kutools for Excel provides a quick and user-friendly way to split and sort comma-separated values in cells. By breaking out numbers using the Split Cells utility, you can dramatically reduce the amount of manual work required. This approach is especially suitable for users looking to optimize their workflow and minimize repetitive actions.
After installing Kutools for Excel, follow these steps:
1. Select the range containing the comma-separated values you want to process. Click Kutools > Merge & Split > Split Cells. In the Split Cells dialog, check Split to Rows in the Type section, and check Other in the Split by section. Make sure to enter a comma (, ) as your delimiter. See screenshot:
2. Click OK. A dialog will appear prompting you to select a cell to place the split values. Choose a destination cell with sufficient empty rows below, then confirm by clicking OK. Your comma-delimited numbers should now be split into separate rows. See screenshot:
3. Select one of the split columns or rows you want to sort, then go to Data > Sort Smallest to Largest or Sort Largest to Smallest. In the Sort Warning dialog, again choose Continue with the current selection and click OK. See screenshot:
4. Repeat step3 to sort additional columns if required. After sorting, you can further process or recombine the data as needed using formula tools like TEXTJOIN.
Tip: This method is particularly useful for repetitive tasks since Kutools automates many manual splitting steps. If you have long lists or need to process multiple cells at once, this solution can greatly improve efficiency.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Sort comma separated values with Kutools for Excel
Sort comma separated values with VBA code
For those who want to fully automate the process—regardless of Excel version—a simple VBA macro can be used to split, sort, and recombine comma-separated numbers in any cell. This solution is ideal for users dealing with repetitive tasks or large data volumes where manual options are impractical.
Advantages: VBA enables you to process large datasets, handle many cells in one go, and repeat the operation as needed. However, running macros requires enabling macros in your workbook and a basic familiarity with the VBA editor. For multi-user or highly automated environments, this can be the most scalable approach.
Note: Always save your work before running new macros and, if possible, test the macro on a copy of your data to prevent accidental loss.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the editor, click Insert > Module, then paste the following code into the Module:
Sub SortCommaSeparatedNumbers()
Dim rng As Range
Dim cell As Range
Dim arr As Variant
Dim sortedArr As Variant
Dim temp As String
Dim i As Long, j As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range to sort comma-separated numbers", xTitleId, rng.Address, Type:=8)
Application.ScreenUpdating = False
For Each cell In rng
If Not IsEmpty(cell.Value) Then
arr = Split(cell.Value, ",")
' Bubble Sort
For i = LBound(arr) To UBound(arr) - 1
For j = i + 1 To UBound(arr)
If Val(arr(i)) > Val(arr(j)) Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
End If
Next j
Next i
cell.Value = Join(arr, ",")
End If
Next cell
Application.ScreenUpdating = True
MsgBox "Sorting done!", vbInformation, xTitleId
End Sub2. Close the VBA editor. Return to Excel and press Alt + F8, select SortCommaSeparatedNumbers, then click Run. A dialog will prompt you to select the cell range to process. After you select your range and confirm, the script will process each cell, automatically sorting the numbers inside them and recombining them with commas.
Troubleshooting and tips: This macro sorts values as numbers—even if your commas are followed by spaces. If cells contain non-numeric entries, the macro sorts those as well but may yield unexpected results. To sort in descending order, adjust the comparison operator in the code from If Val(arr(i)) > Val(arr(j)) Then to If Val(arr(i)) < Val(arr(j)) Then. If your list includes text or mixed data, consider adding type checking. Be sure to backup data before running batch operations.
In summary, sorting comma-separated numbers within cells can be handled in multiple ways with Excel, each suitable for different scenarios and Excel versions. Text to Columns or Kutools are practical for manual or tool-assisted solutions, while VBA provides efficient, automatic processing—especially for frequently updated or large datasets. Consider your actual needs, data size, and Excel version to choose the right solution. Should errors or unexpected behavior occur, double-check delimiters, cell formatting, and make sure to clear any extra spaces before running methods above. Regularly saving your work and working on data copies helps avoid accidental loss or overwriting of important information.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in