Sorting an alpha-numerical list in excel?

Question

I have a list of data in excel. The list is nearly 2000 lines long so manual sorting is not really an option. I enter the number is order, but I accidently sorted the list and it came out like this:

1232
1233
1234
1235
1236
1237
1238
1239
1240
1234cr
1235rb
1237cr
1236cr

I need to sort the list like this:
1232
1233
1234
1234cr
1235
1235rb
1236
1236cr
1237
1237cr

I cannot find any way in swift to fix this. Is there a formula that will allow for sorting by the number first and then the suffix?

Answer 1

Here is another approach that will sort as you wish simply by double clicking the column that you wish to sort.



Copy this event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, LastRow

LastRow = Application.Cells. _

SpecialCells(xlCellTypeLastCell).Row

Application.ScreenUpdating = False

For i = 1 To LastRow

For j = 1 To Len(Cells(i, Target.Column))

If IsNumeric(Mid(Cells(i, Target.Column), j, 1)) Then

tval = tval & Mid(Cells(i, Target.Column), j, 1)

End If

Next j

Cells(i, "IV").Value = tval

tval = ""

Next i

Columns("A:IV").Select

Selection.Sort Key1:=Range("IV1"), Order1:=xlAscending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Columns("IV:IV").ClearContents

Cells(1, Target.Column).Select

End Sub



Select the worksheet containing the data that you wish to sort and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button w/white 'x' - top right).



To sort, double click any cell in the column containing the data to be sorted. If your data changes, or gets compromised again, simply double click any cell in the column again.

Answer 2

I hate when that happens.



OK, I use Open Office which works exactly like Excel. Formatting the numbers to text does not work. The only way I know to do this is the "cheesy" method.

1) save your file.

2) convert the numbers to text (select the entire column, Format/cells/Category=text)

3) Insert two columns (I will call them B and C) to the right of your number column (I will call it 'A')

4) Assuming your data starts at row 2, in cell B2 type =LEFT(A2;4) This will be just the left four characters (I am assuming all your numbers are 4 digits...)

5) In cell C2 type =LEN(A2)

6) copy B2 and C2 down to all the cells, to B2000 and C2000 (or however many rows you have).

7) Highlight everything you want to sort, but sort first by col B, then C, then A

8) delete columns B, C



Cheesy, but it works.

5) Grab all the cells and sort first by column

Answer 3

There is probably a more elegant way of doing it, but the technique below worked on your sample data.



In the closest empty column, enter the formula below.



=A2&"aa"



This assumes your data is in column A. Now copy the formula down the column, and sort by this column. You can hide this column if you don't want it seen/printed.

Answer 4

Re-define the column's contents from Numbers ONLY to text (which includes numbers, and letters, etc.).