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.

0
andersmw (2)
3/2/2007 10:26:13 PM
excel 39879 articles. 2 followers. Follow

3 Replies
900 Views

Similar Articles

[PageSpeed] 1

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,
>
> 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.


0
pashurst (2576)
3/2/2007 10:38:53 PM
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" <pashu...@auditel.net> 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, ander...@gmail.com 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 -


0
pashurst (2576)
3/3/2007 12:29:56 AM
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

<andersmw@gmail.com> wrote in message 
news:1172874373.156937.216210@j27g2000cwj.googlegroups.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.
> 


0
KC
3/3/2007 4:29:36 AM
Reply:

Similar Artilces:

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Vb.net 2008 ContextMenuStrip logical error when running code
Greetings, I have a connectmenustrip item that when clicked runs the following code (see below) Now if the event is called by the button i.e. cmdDeleteingBooking.Click the linq query returns the appropriate value. However when called by cntMnuCancelBookingItem.Click is returns 0 even if a checkbox is of 'TRUE' value. Debugging shows the code runs exactly the same code (which loops around rows in a datagridview checking if the checkbox has been checked). Could someone explain the reasoning why the same code would return different results? Private Sub cmdDelete...

RMS Status Codes
Just wondering if anyone has a list of what the RMS Batch.Status codes 0-15 mean? I can't find them defined anywhere. I'm specifically looking at how to identify Blind Closes so I don't count them in totals until they'e been closed. Thanks! -Zim There is a Knowledge Base Article that covers the different Batch Status codes from 0 - 31. Just search for 'batch status codes' -- Robert Armstrong RMS Systems Inc. www.retail-pos.com "Zim" <Zim@discussions.microsoft.com> wrote in message news:C72515DB-AD45-4C7D-B8DE-0A18E4A6D0D0@micr...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

Excel worksheet with VBE codes don't work elsewhere
Hi, Some of my excel worksheets with embedded controls and VBA codes don' work when I open it on another PC. Is there another way to make i work? Thx -- lazybea ----------------------------------------------------------------------- lazybear's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3519 View this thread: http://www.excelforum.com/showthread.php?threadid=54955 Specifically what problems are you having? Saying "don't work" means absolutely nothing. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ww...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Conitional Formatting
Hello. I have two fields in a subform, "User" and "IT Announcement" I would like to do conditional formatting to this effect: On ther "User" field: If "IT Announcement" = Yes then make the "User" field turn blue (I would choose the color blue from the conditional formatting selection. How would you write this? Thanks. Iram/mcp On Tue, 23 Oct 2007 14:59:01 -0700, Iram wrote: > Hello. > I have two fields in a subform, "User" and "IT Announcement" > I would like to do conditional formatting to this effect: > On...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was authored by someone else. Track Changes is switched off, all changes have been accepted, and everything looks as it should in whichever view I happen to choose (Print Layout, Draft, whatever). But when I click to show formatting symbols (in whatever view) a whole lot of old changes - deletions AND insertions, ostensibly all accepted, and from before the document got to me - appear in the document, making it quite tricky to work with. These old changes are impervious to anything I try to do with them E...

Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or higher.either text or cell shade. just so it catches the users attention. im running excel 2000. and i have this currently in the cell that i want to aplly this to: =HLOOKUP(D20,'Hidden Data'!GZ10:HB11,2,0)*MAX(15,E20) have you tried conditional formating? format>conditional formating >-----Original Message----- >I need a cell to change color if the value inside the cell reaches 50 or >higher.either text or cell shade. just so it catches the users attention. >im running excel 2000. and i ...