Here is a macro that does an in-cell sort of numbers.
The macro ask for a range of cells to be sorted - when the selection box opens, just select the cells and click OK.
If you always have the same range of cells to sort, you could hard code the range.
If the range was always similar, say same columns but different numbers of rows each time, it would be possible to add code to make the selection automatic.
'numbers' are sorted 1-2-3-4-5-6-7-8-9-0
To run the macro, I suggest you add a button to your source worksheet
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)
In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'
In the code window that opens enter this:
Private Sub CommandButton1_Click()
Dim rngToSort As Range
Dim strChar As String
Dim rngCell As Range
Dim varSortArray() As Variant
Dim strText As String
Dim strRes As String
Dim m As Integer
Dim n As Integer
On Error Resume Next
'get cell selection
Set rngToSort = Application.InputBox(Prompt:="Select cells to be sorted", _
If rngToSort Is Nothing Then
MsgBox "Please select a valid range of cells"
On Error GoTo ErrHnd
'loop through all cells in range
For Each rngCell In rngToSort
If IsNumeric(rngCell.Value) Then
strText = rngCell.Text
'resize array to accept string
ReDim varSortArray(Len(strText), 1)
'clear result string
strRes = ""
'put characters into array
For n = 1 To Len(strText)
strChar = Mid(strText, n, 1)
varSortArray(n, 1) = strChar
'add code number
'but make 0 largest
If strChar = "0" Then
varSortArray(n, 0) = 58
varSortArray(n, 0) = Asc(Mid(strText, n, 1))
'numbers are codes 48 to 57
'but we moved 0, so use 49 to 58
'get lowest first
For m = 49 To 58
For n = 1 To Len(strText)
If varSortArray(n, 0) = m Then
'add character to result string
strRes = strRes + varSortArray(n, 1)
'clear value so we don't re-use it
varSortArray(n, 0) = 0
'save sorted string as a number
rngCell.Value = CDbl(strRes)
Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().
Click Save from the Visual Basic Menu.
Alt+f11 takes you back to the main Excel window.
Right click the button and Edit the name to something meaningful
As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'
After selecting any cell, the new command button should now respond to a click and run the macro.
This is what I got on my example data: