How to sort by first number?
Posted by Ken on March 01, 2000 12:10 PM
Hi,
How do sort a column of numbers of different size ex.10,1000,200,2000 by the first first character in the number, not by the numbers value? In other words sort all the 1's first then the 2's etc..
Posted by Chris on March 01, 2000 1:43 PM
The easy way would be to sort on a hidden column containing a formula to take the leftmost character. An example would be:
=LEFT(A1,1)
HTH,
Chris
Posted by Mark on March 02, 2000 6:26 AM
Ken
If you convert the numbers to text, a standard sort will do what you want.
Example
You will need a blank column to work with, so insert a new colum A. For the data you want to sort in B5:K25, with the numbers you want to sort on in B5:B25,
enter =TEXT(B5,"##") in cell A5 and fill down through A25. Then copy the selected data in column A, then pull down the Edit menu and select Paste Special..., Values.
Select B5:K25, pull down the Data menu, select Sort..., and sort on column B.
Mark
Posted by Mark on March 02, 2000 12:05 PM
Ken
If I give you that much detail, I should at least not make errors - and its not even Monday.
If you convert the numbers to text, a standard sort will do what you want.
Example: Say the data you want to sort is in A5:J25 with the numbers you want to sort on in column A.
You will need a blank column to work with, so insert a new column A. The data you want to sort is now in B5:K25, with the
numbers you want to sort on in B5:B25,
enter =TEXT(B5,"##") in cell A5 and fill down through A25. Then copy the selected data in column A, then pull down the Edit
menu and select Paste Special..., Values.
Select A5:K25, pull down the Data menu, select Sort..., and sort on column A.
Since column A is no longer needed you can delete it.
Mark
