Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
I don't think you have any alternative but to use a "helper" column with the
first digit. "laura3511" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
Format the data as Text then sort. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel add-ins) "laura3511" 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
Formatting as TEXT don't work!
"Jim Cone" wrote: Format the data as Text then sort. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel add-ins) "laura3511" 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
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 On Mon, 25 Jun 2007 10:17:01 -0700, laura3511 wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
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" wrote in message Formatting as TEXT don't work! "Jim Cone" wrote: Format the data as Text then sort. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel add-ins) "laura3511" 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I sort by the first digit of a number?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
How to validate a cell to insert from 9 digit number to 13 digit number. | Excel Worksheet Functions | |||
Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel | |||
When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) |