You can do it manually as follows. Assume your data is in A1:A10.
Insert a new column B to the right of this and enter in B1
=TEXT(A1,"0")
and copy down to B10. Next, select B1:B10 and move the mouse
over the right hand selection border so that it turns to a
four-pointed arrow. Hold down the RIGHT mouse button, drag the
selection one column to the right and then back to the left onto
itself and release the right mouse button. In the popup menu that
displays, choose "Copy Here As Values Only". With B1:B10 selected, go
to the Data menu and choose Sort. If you get a sort warning about data
being next to the selection, choose Expand Select and click Sort. In
the next Sort dialog, choose Column B as the Sort By setting. Click
Sort. You'll get a warning asking whether to treat things that look
like numbers as number or to treat them as text. Choose the "treat as
text" option and click OK. Now, your numbers will be sorted in the
desired order.

You can automate this with:

Sub AAA()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Range("A1:A10") '<<<< CHANGE
SourceRange(1, 2).EntireColumn.Insert
Set DestRange = SourceRange(1, 2).Resize(SourceRange.Rows.Count)
With DestRange
.Formula = "=TEXT(A1,""0"")"
.Copy
.PasteSpecial xlPasteValues
.Sort key1:=DestRange(1, 1), order1:=xlAscending
End With
End Sub

Just change the reference to SourceRange to the appropriate range.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 21 Mar 2009 13:25:56 -0400, Fred Holmes wrote:

Excel 2000 (or upgrade?)

Is there any way to get Excel to sort numbers (accounting categories)
as text, i.e., the sorted output would look like:

5
50
501
5011
5012
5013
51
511
5111
5112
5113
6

I get the correct sorting action if I replace the numbers with, e.g.,
letters A through J.

I don't want to fill in the right-hand digits with zeros, because that
would break something.

Simply formatting the cells as text doesn't work.

I'm using the account numbers, via cell range reference, in a SUMIF
formula's critera, that isn't hard coded, but gets the accounting
category to subtotal from the data in the cells to be sorted.

=FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10),2 )

I don't want to break the above formula, where RC8 is the cell that
contains the account number. C8 contains the accounting category for
the individual transaction being posted, which account number is four
digits. If the account number has less than four digits, the value is
a sum of all transactions that have those first digits in their
account number.

TIA

Fred Holmes