## How to sort numbers within a cell in Excel?

It is easy and common for us to sort numbers in a list of column, but have you ever tried to sort numbers within a single cell? May be there is no good way for you except arrange them one by one, here, I will talk about how to sort numbers within cells in Excel.

**Sort numbers within cells with formula**

**Sort numbers within cells with User Defined Function**

**Sort numbers which separated by commas within cells with VBA code**

** Sort numbers within cells with formula**

To sort numbers within cells in a worksheet, you can apply the following long formula, please do as this:

**1**. Next to your data, please enter the following formula, in this example, I will type it into cell C1, see screenshot:

**=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. **Then press **Ctrl + Shift + Enter** keys together, then drag the fill handle over to the range that you want to apply this formula, and you will get the numbers have been sorted from small to large. See screenshot:

**Notes:**

1. If the digit of the number is more than 15 in the cell, this formula will not get the correct result.

2. If you want to sort the numbers in descending order, you can use this formula:** =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))).**

3. In the above formulas, A1 indicates the cell which contains the numbers you want to sort, you can change it to your need.

** Sort numbers within cells with User Defined Function**

As there are some limitations of the formula, you can use the following **User Defined Function** to sort numbers in cells longer than 15 digits.

**1**. Hold down the **ALT + F11** keys, and it opens the **Microsoft Visual Basic for Applications window**.

**2**. Click **Insert** > **Module**, and paste the following code in the **Module Window**.

**VBA code: Sort numbers within cells**

```
Function SortNumsInCell(pNum As String, Optional pOrder As Boolean) As String
'Update 20140717
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.** Then save and close this code, go back to your worksheet, and enter this formula **=sortnumsincell(A1)** into a blank cell next to your data, see screenshot:

**4**. And then drag the fill handle to the cells that you want to contain this formula, and all the numbers in the cells have been sorted in ascending order as following screenshot shown:

**Note**: If you want to sort the numbers in descending order, please enter this formula **=sortnumsincell(A1,1).**

** Sort numbers which separated by commas within cells with VBA code**

If your numbers are separated by certain characters such as comma, semicolon, period and so on as following screenshot, how could you sort them in cells? Now, I introduce a VBA code for you to sort them.

**1**. Hold down the **ALT + F11** keys to open the **Microsoft Visual Basic for Applications window.**

**2**. Click **Insert** >** Module**, and paste the following code in the **Module Window**.

**VBA code: Sort numbers are separated by commas within cells**

```
Sub SortNumsInRange()
'Update 20140717
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
On Error Resume Next
xTitleId="KutoolsforExcel"
Set WorkRng=Application.Selection
Set 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, ",")
Next
End Sub
```

**3**. Then press **F5** key to run this code, and then select your cells which contain the numbers in the popped out prompt box, see screenshot:

**4**. And then click **OK**, all the numbers in the cells have been sorted ascendingly in the original range.

**Note**: You can change the comma “,” to any other characters as you need in the above code. And this code only can sort data ascendingly.

**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!