How to sort numbers within a cell in Excel?
When working with Excel, sorting numbers across a range of cells or within a column is a common and straightforward task. However, there are situations where you might need to sort the numbers that all reside within a single cell—for example, if you receive imported data, consolidated reports, or export files where multiple numbers are stored together without separation into separate cells. Sorting numbers manually in each cell can be inefficient and error-prone, especially when handling large amounts of data or cells containing many digits. By using specific techniques, you can quickly reorder numbers within individual cells to meet data organization or analysis requirements.
In this article, you'll discover several practical ways to sort numbers within cells in Excel, including formula-based methods, user-defined functions, and VBA code. Each approach has its own strengths and is suited to different scenarios, so you can choose the solution that best fits your needs.
Sort numbers within cells with a formula
Sort numbers within cells with User Defined Function
Sort numbers which are separated by commas within cells with VBA code
Sort numbers within cells using Excel's Flash Fill feature
Sort numbers within cells with a formula
For simple cases where all the numbers in a cell are single digits and there are no delimiters, you can use an array formula to automatically sort the digits in a cell. This approach is helpful when working with codes, IDs, or any scenario where single digits in a cell need to be rearranged in order.
Please follow these detailed steps:
1. Insert a new column next to your data. For example, if your numbers are in cell A1, click cell C1 and enter the following formula:
=TEXT(SUM(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1)))
2. As this is an array formula, confirm the entry by pressing Ctrl + Shift + Enter. Curly braces will appear around the formula in the formula bar, indicating it is evaluated as an array.
After that, drag the fill handle down to copy the formula to other cells where you need the sorting function. The numbers in each cell will be sorted in ascending order automatically. See example below:
For easier use, double-check that you are referencing the correct cells and that your original data contains only digits, as mixed text or additional characters may cause issues.
Notes:
- If a cell contains more than 15 digits, this formula may not work accurately due to Excel's default limit for numerical precision. For longer strings or more complex digit arrangements, consider using VBA solutions or a User Defined Function instead.
- To sort numbers in descending order, you can replace SMALL with LARGE in the formula, and use:
=TEXT(SUM(LARGE(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1)))
This formula works the same way, but sorts digits from largest to smallest.
- In these formulas, A1 refers to the cell containing the numbers you wish to sort. If your data is in a different cell, simply update the reference accordingly.
- If you encounter a #VALUE! error, make sure that the cell only contains single digits and no spaces or other characters.
- When applying this method to a large dataset, check that your system’s calculation options are set to automatic, so the formula outputs update correctly as you copy or modify data.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Sort numbers within cells with a User Defined Function
In certain situations, such as when dealing with numbers longer than 15 digits or needing more flexibility, a User Defined Function (UDF) provides a robust alternative. UDFs offer a way to automate sorting inside cells, especially if your requirements exceed what standard Excel formulas can handle.
This solution is well-suited to users who often work with imported data columns or codes packed into single cells, where the cell length may be substantial and numbers may need to be sorted repeatedly.
Here is how to implement it:
1. Press and hold ALT + F11 to launch the Microsoft Visual Basic for Applications (VBA) window.
2. In the VBA window, click Insert > Module, then copy and paste the following code into the newly created module window:
VBA code: Sort numbers within cells
Function SortNumsInCell(pNum As String, Optional pOrder As Boolean) As String
'UpdatebyExtendoffice
Dim xOutput As String
For i = 0 To 9
For j = 1 To UBound(VBA.Split(pNum, i))
xOutput = IIf(pOrder, i & xOutput, xOutput & i)
Next
Next
SortNumsInCell = xOutput
End Function
3. After pasting the code, save and close the VBA editor. Return to your worksheet, and in an empty cell adjacent to your data (such as B1 or C1), enter:
=sortnumsincell(A1)
4. Drag the fill handle to extend the function to other cells as needed. All numbers within each targeted cell will now be sorted in ascending order as shown below:
Note: If you want the numbers to be sorted in descending order, enter the following formula instead:
=sortnumsincell(A1,1)
The additional argument (1) tells the function to perform a descending sort.
This UDF-based solution allows for greater flexibility and precision with larger or more complex datasets, and can help avoid common formula errors due to digit limitations.
If you encounter compile errors, double-check that your module code was pasted correctly and that the function name matches your worksheet formula.
Sort numbers which are separated by commas within cells with VBA code
When your numbers within a cell are separated by specific delimiters—such as commas, semicolons, or even periods—the built-in formula solutions might not be suitable. In these cases, a customized VBA code can be applied to sort the numbers efficiently, no matter how many cells you are processing.
Applicable scenarios include lists exported from databases, collected survey responses, or any dataset where numeric values are stored in a single cell and separated by a consistent character.
Follow these steps to run VBA code for sorting:
1. Press ALT + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the code below into the module window:
VBA code: Sort numbers separated by commas within cells
Sub SortNumsInRange()
'Update20140717Dim Rng As RangeDim WorkRng As RangeDim Arr As VariantOn Error Resume NextxTitleId = "KutoolsforExcel"
Set WorkRng = Application.SelectionSet WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set objArrayList = CreateObject("System.Collections.ArrayList")
For Each Rng In WorkRng Arr = VBA.Split(Rng.Value, ",")
For i =0 To UBound(Arr)
xMin = i For j = i +1 To UBound(Arr)
If Arr(xMin) > Arr(j) Then xMin = j End If Next j If xMin <> i Then temp = Arr(i)
Arr(i) = Arr(xMin)
Arr(xMin) = temp End If Next i Rng.Value = VBA.Join(Arr, ",")
NextEnd Sub3. To execute the code, press F5. A prompt will appear, allowing you to select the range containing the cells you want to sort.
4. After confirming with OK, the code will sort the numbers in all selected cells in ascending order, and the changes will be made in place without affecting other cell content.
Note: If your numbers are separated by another character (for example, a semicolon or space), simply replace the comma “,” in Split(Rng.Value, ",") and Join(Arr, ",") with your chosen delimiter in both locations within the code.
This code only sorts in ascending order. To sort in descending order, you may need to adjust the sorting logic in the VBA code. When using VBA, always save your workbook before running code, as changes are immediate and cannot be undone from the Undo feature.
If a cell contains non-numeric content, the code may not generate the intended result; check your data for mixed content before running.
Sort numbers within cells using Excel's Flash Fill feature
For small datasets or whenever numbers within a cell follow a predictable pattern, Excel's Flash Fill feature offers a quick, manual solution. This approach is particularly effective when you only need to sort numbers within a few cells, and the structure is simple.
Here's how to use Flash Fill for sorting numbers inside cells:
1. In a new column adjacent to your data, manually type the sorted version of the numbers from your first cell (for example, if A1 contains 8371, type 1378 in cell B1).
2. Select the next cell in the same column. Begin typing the sorted version for the next entry. After a couple of examples, press Ctrl + E (Flash Fill shortcut). Excel will automatically recognize the pattern and fill the remaining cells according to your sorting example.
This technique is fast but best suited for short lists or when the data pattern is very regular. It does require that you type at least a couple of sorted results for Excel to detect the intended transformation.
Flash Fill does not work reliably for complex patterns or if your numbers are separated by special characters. Always review the automatically generated results for accuracy.
Note: This method may not work in all cases.
When choosing an approach, consider the structure of your data (single digits versus numbers with separators), the length of the data string, and the number of cells to process. Formula-based methods work best for short, delimiter-free strings, whereas VBA or UDF solutions offer greater flexibility and power for complex or lengthy data arrangements.
Double-check your original data for hidden spaces, text, or formatting issues before applying any solution. After sorting the numbers, it's good practice to verify the output by comparing a few samples against the original input.
If you encounter any issues, please ensure your Excel version supports array formulas (Excel 2016 or later improves compatibility), and that macros are enabled for VBA methods. If sorting is not working as expected, try adjusting the formula or VBA code to fit your cell structure.
Related articles:
How to sort numbers with hyphens in Excel?
How to sort data by the most frequent value in Excel?
How to sort email address by domain in Excel?
How to sort rows to put the blank cells on top in Excel?
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