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

[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.

 

[Guest]

I don't think you have any alternative but to use a "helper" column with the
first digit.

 

[Jim Cone]

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.

 

[Guest]

Formatting as TEXT don't work!

 

[Gord Dibben]

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

 

[Jim Cone]

I'm not having a good day. <g>
Doesn't work for me either unless you go thru the F2 thing on each cell.
It will work if the cells are formatted as text before manual data entry.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Toppers" <[email protected]>
wrote in message
Formatting as TEXT don't work!

 

[Paul D. Simon]

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.

 

[Paul D. Simon]

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.