Numbers to sort as text

[Fred Holmes]

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

 

[Chip Pearson]

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)

 

[Fred Holmes]

Many thanks for the instructions. I haven't tried it yet to see what
is actually happening. It appears that you have created macro/VBA
code to fill in a separate column of data (Column B) on which to
actually sort, rather than sorting on the real data (Column A). There
is, of course, always the option to manually create such a column, but
it is a hard-coded column B (copy as values only)?? In what I am
doing there will be intances of my wanting to change the accounting
category on the fly, and have the sort work. I will experiment with
this code and see what I can learn from it and how to make it perform.

My nirvana would be to sort it either way on user's choice. By having
two sorting columns, I guess this provides that capability. Hopefully
the artificial sorting column (Column B) can be a hidden Column. I
wouldn't want it to display in printouts.

It would be nice if the Sort would work on the _displayed_ number, and
there were a display formatting code to fill in _trailing_ zeros
(rather than leading zeros, for which there is display formatting
code)

Many thanks for all that you and the other MVPs do. Especially on
weekends.

Fred Holmes

 

[Shane Devenshire]

Hi,

Suppose your data is in A1A100 in B1 enter the formula
=LEFT(A1,9)
copy this down and sort both columns (or these and others) by this new column.

 

[Bernd P]

Hello,

If you do not have too many records I suggest to use my UDF GSort with
the (S)tring comparison parameter.

A worksheet function approach:
http://www.sulprobil.com/html/sorting.html
(You would need to insert "" & before the second argument of Countif
to enforce string comparison.)

Regards,
Bernd

 

[Chip Pearson]

There is no option to sort things that look like numbers as text
instead of numbers. The only way is to jump through the hoops as I
described.

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