NAICS Code Cell Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

andersmw@gmail.com - 02 Mar 2007 22:26 GMT

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 to this Message

Pete_UK - 02 Mar 2007 22:38 GMT

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, ander...@gmail.com wrote:

> Hello,
>
[quoted text clipped - 24 lines]
>
> Thank you in advance for your help.

Reply to this Message

Pete_UK - 03 Mar 2007 00:29 GMT

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

Pete

> 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
[quoted text clipped - 39 lines]
>
> - Show quoted text -

Reply to this Message

KC Rippstein - 03 Mar 2007 04:29 GMT

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

> Hello,
>
[quoted text clipped - 24 lines]
>
> Thank you in advance for your help.

Reply to this Message