Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
NAICS Code Cell Format
Hello,
I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the numbers sort properly? Thank you in advance for your help. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
NAICS Code Cell Format
Though these look like numbers, they are being stored by Excel as text
values. One way to convert them is to select a blank cell somewhere and click <copy, then highlight the offending "numbers" and Edit | Paste Special | Add (check) | OK then <Esc. You should see the numbers align to the right of the cell, then you can apply your sort again. Hope this helps. Pete On Mar 2, 10:26 pm, wrote: Hello, I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the numbers sort properly? Thank you in advance for your help. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
NAICS Code Cell Format
Just to follow up (for the archives), he emailed me directly to say
that "... the process worked without a hitch...". Pete On Mar 2, 10:38 pm, "Pete_UK" wrote: Though these look like numbers, they are being stored by Excel as text values. One way to convert them is to select a blank cell somewhere and click <copy, then highlight the offending "numbers" and Edit | Paste Special | Add (check) | OK then <Esc. You should see the numbers align to the right of the cell, then you can apply your sort again. Hope this helps. Pete On Mar 2, 10:26 pm, wrote: Hello, I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the numbers sort properly? Thank you in advance for your help.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
NAICS Code Cell Format
You probably know this, but for the benefit of the doubt, thought I'd throw
this note in here. The true structure of those NAICS codes is that the first 2 numbers are a broad category, and any numbers to the right introduce a more narrow category, up to 6 digits total. They had the structure set up the way they did (as a text outline) so that it would properly sort related sub-items directly underneath their parents. You actually hit the nail on the head when you suggested the imaginary decimal: 10 as a "parent", then 10.1 as a "child" node, etc. -KC wrote in message oups.com... Hello, I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the numbers sort properly? Thank you in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Code in Cell format ? | Excel Discussion (Misc queries) | |||
Time code cell format | Excel Worksheet Functions | |||
How to refer to a cell format code? | Excel Discussion (Misc queries) | |||
Format the Cell into a telephone number but using country code | Excel Discussion (Misc queries) | |||
zip code format | Excel Discussion (Misc queries) |