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.

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.

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 -

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.