How do I sort by the first digit of a number?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to sort a list of part number like this:
10, 54, 94, 101, 1800, 200, 2000, 500

The only way I can find to sort them is:
10, 54, 94, 101, 200, 500, 1800, 2000

What I need is them sorted by the first digit and then the entire number:
10, 101, 1800, 200, 2000, 54, 500, 94

Is there any reasonable way to do this without manually creating another
column that has the first digit in it?

Any help would be greatly appreciated.
 
Format the data as Text then sort.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel add-ins)


"laura3511" <[email protected]>
wrote in message
I'm trying to sort a list of part number like this:
10, 54, 94, 101, 1800, 200, 2000, 500

The only way I can find to sort them is:
10, 54, 94, 101, 200, 500, 1800, 2000

What I need is them sorted by the first digit and then the entire number:
10, 101, 1800, 200, 2000, 54, 500, 94

Is there any reasonable way to do this without manually creating another
column that has the first digit in it?
Any help would be greatly appreciated.
 
Assuming numbers are in column A

In B1 enter this and copy down.

=CODE(LEFT(A1,1))

Sort on column B


Gord Dibben MS Excel MVP
 
Hi Laura,

This macro will change existing numbers to text:

Sub TextToNumbers()
Selection.TextToColumns Destination:=ActiveCell,
DataType:=xlFixedWidth, FieldInfo:=Array(0, 2)
End Sub

Highlight the cells containing numbers that you want to change to text
and run the code above. Once that's done, you can sort them, and they
will then indeed be in the order you requested.
 
For some reason, when I posted my response, this site broke the code
line into 2 lines, which will not work if you do a copy and paste.
The code between Sub and End Sub should all be on 1 line, not 2 lines.
 
Back
Top