NAICS Code Cell Format

[andersmw]

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.

 

[Pete_UK]

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

 

[Pete_UK]

Just to follow up (for the archives), he emailed me directly to say
that "... the process worked without a hitch...".

Pete

 

[KC Rippstein]

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