Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Sorting numbers and text separately
Excel 2003
When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#2
|
|||
|
|||
If you want it sorted as text, then you have to change the format to text.
There is no option to sort as text or numbers. There is a small glitch I noticed that when you changed the cells' format to text it still sorts it as numbers. You have to manually double click on each cell and hit enter to make the format "set in". After you do this it will sort as text. There are ways to avoid this problem in the future. 1) change the cells format you know are text before you begin typing. 2) use an apostrophe before all numbers to tell the computer it is text; it outputs it without the apostrophe (For Example type '12 instead of 12). hope this helps! "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#3
|
|||
|
|||
Nope.
No matter how or when the data is entered or when the format is set to text, the text that looks like numbers sorts separately from other text. It used to be that a dialog box asked how I wanted it sorted. I don't know if that was in an earlier version of Excel (currently 2003) or if I told it to quit asking me and I don't know how to turn it back on. Specifically, I am experimenting with sorting a mix of 5-digit and 9-digit zip codes. All are formatted as text. All of the 5-digit zip codes sort above all of the 9-digit zip codes thus: 01234 12345 23456 56789 12000-2614 45623-4512 instead of the desired: 01234 12000-2614 12345 23456 45623-4512 56789 Tim C "Sloth" wrote in message ... If you want it sorted as text, then you have to change the format to text. There is no option to sort as text or numbers. There is a small glitch I noticed that when you changed the cells' format to text it still sorts it as numbers. You have to manually double click on each cell and hit enter to make the format "set in". After you do this it will sort as text. There are ways to avoid this problem in the future. 1) change the cells format you know are text before you begin typing. 2) use an apostrophe before all numbers to tell the computer it is text; it outputs it without the apostrophe (For Example type '12 instead of 12). hope this helps! "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#4
|
|||
|
|||
Tim,
You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#5
|
|||
|
|||
Tim,
Oops. Don't do that as I said. If your list starts in A2, the formula should be: =TEXT(A2,"00000") -- Earl Kiosterud www.smokeylake.com "Earl Kiosterud" wrote in message ... Tim, You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#6
|
|||
|
|||
Nope.
No matter how or when the data is entered or when the format is set to text, the text that looks like numbers sorts separately from other text. It used to be that a dialog box asked how I wanted it sorted. I don't know if that was in an earlier version of Excel (currently 2003) or if I told it to quit asking me and I don't know how to turn it back on. Specifically, I am experimenting with sorting a mix of 5-digit and 9-digit zip codes. All are formatted as text. All of the 5-digit zip codes sort above all of the 9-digit zip codes thus: 01234 12345 23456 56789 12000-2614 45623-4512 instead of the desired: 01234 12000-2614 12345 23456 45623-4512 56789 Tim C "Earl Kiosterud" wrote: Tim, You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#7
|
|||
|
|||
Tim,
Does "nope" mean that using my formula in a helper column didn't work? It can only result in text, and thus could only be sorted alphabetically, it seems to me. That just has to work for you. HAS to! :) The dialog box to which you refer may be the one you get with Data - Sort. The buttons on the toolbar are quick sort buttons, and don't give a dialog. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Nope. No matter how or when the data is entered or when the format is set to text, the text that looks like numbers sorts separately from other text. It used to be that a dialog box asked how I wanted it sorted. I don't know if that was in an earlier version of Excel (currently 2003) or if I told it to quit asking me and I don't know how to turn it back on. Specifically, I am experimenting with sorting a mix of 5-digit and 9-digit zip codes. All are formatted as text. All of the 5-digit zip codes sort above all of the 9-digit zip codes thus: 01234 12345 23456 56789 12000-2614 45623-4512 instead of the desired: 01234 12000-2614 12345 23456 45623-4512 56789 Tim C "Earl Kiosterud" wrote: Tim, You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#8
|
|||
|
|||
Earl,
In Excel 2003 Help, there is an article, "Sorting a range that contains numbers stored as text." It discusses the differences between the choices "Sort numbers, and numbers stored as text, separately" and "Sort anything that looks like a number as a number." But it does not say anywhere in the article (or anywhere in Excel Help or in MSKB or the group or the internet that I can find) where one has the opportunity to make this choice. I did find a registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Options with the value: "SortTextAsNumbers"=dword:00000001 When SortTextAsNumbers is set to 0, it sorts as you describe, with all numbers formatted as numbers at the top, then all of the text, including text that looks like numbers, sorted together at the bottom. When SortTextAsNumbers is set to 1, it sorts as I described, with all numbers AND text that looks like numbers sorted together at the top, with all other text sorted together at the bottom. But I cannot find where in Excel I can make this choice. Once upon a time, a dialog box would pop up when you tried to sort data that contained text that looks like numbers, giving you the choice. But it doesn't currently pop up on my computer or on any other computer in the building. (We are running Office 2003 Pro SP1 on Windows XP Pro SP2 with latest updates on both.) What happened to the dialog box? Or where else can I set the option? Tim C "Earl Kiosterud" wrote: Tim, Does "nope" mean that using my formula in a helper column didn't work? It can only result in text, and thus could only be sorted alphabetically, it seems to me. That just has to work for you. HAS to! :) The dialog box to which you refer may be the one you get with Data - Sort. The buttons on the toolbar are quick sort buttons, and don't give a dialog. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Nope. No matter how or when the data is entered or when the format is set to text, the text that looks like numbers sorts separately from other text. It used to be that a dialog box asked how I wanted it sorted. I don't know if that was in an earlier version of Excel (currently 2003) or if I told it to quit asking me and I don't know how to turn it back on. Specifically, I am experimenting with sorting a mix of 5-digit and 9-digit zip codes. All are formatted as text. All of the 5-digit zip codes sort above all of the 9-digit zip codes thus: 01234 12345 23456 56789 12000-2614 45623-4512 instead of the desired: 01234 12000-2614 12345 23456 45623-4512 56789 Tim C "Earl Kiosterud" wrote: Tim, You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
#9
|
|||
|
|||
Tim,
I've just seen that dialog (and for the first time). I'm using Excel 2002. It comes up with Data - Sort, but not with the sort buttons on the Standard Toolbar. Unfortunately, it appears your 9-digit zip codes will confound this, as the option forces text to sort as a number (with other actual numbers, which will appear first), but your 9-digit codes will not sort correctly whether they're text or numbers formatted with the dash. I've tried it. Too bad there's not an option to sort numbers, and text that looks like numbers, as text. I think maybe you should use a helper column with my formula, and sort on that. Hide it if you want, and have a macro do the sort if you want. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Earl, In Excel 2003 Help, there is an article, "Sorting a range that contains numbers stored as text." It discusses the differences between the choices "Sort numbers, and numbers stored as text, separately" and "Sort anything that looks like a number as a number." But it does not say anywhere in the article (or anywhere in Excel Help or in MSKB or the group or the internet that I can find) where one has the opportunity to make this choice. I did find a registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Options with the value: "SortTextAsNumbers"=dword:00000001 When SortTextAsNumbers is set to 0, it sorts as you describe, with all numbers formatted as numbers at the top, then all of the text, including text that looks like numbers, sorted together at the bottom. When SortTextAsNumbers is set to 1, it sorts as I described, with all numbers AND text that looks like numbers sorted together at the top, with all other text sorted together at the bottom. But I cannot find where in Excel I can make this choice. Once upon a time, a dialog box would pop up when you tried to sort data that contained text that looks like numbers, giving you the choice. But it doesn't currently pop up on my computer or on any other computer in the building. (We are running Office 2003 Pro SP1 on Windows XP Pro SP2 with latest updates on both.) What happened to the dialog box? Or where else can I set the option? Tim C "Earl Kiosterud" wrote: Tim, Does "nope" mean that using my formula in a helper column didn't work? It can only result in text, and thus could only be sorted alphabetically, it seems to me. That just has to work for you. HAS to! :) The dialog box to which you refer may be the one you get with Data - Sort. The buttons on the toolbar are quick sort buttons, and don't give a dialog. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote in message ... Nope. No matter how or when the data is entered or when the format is set to text, the text that looks like numbers sorts separately from other text. It used to be that a dialog box asked how I wanted it sorted. I don't know if that was in an earlier version of Excel (currently 2003) or if I told it to quit asking me and I don't know how to turn it back on. Specifically, I am experimenting with sorting a mix of 5-digit and 9-digit zip codes. All are formatted as text. All of the 5-digit zip codes sort above all of the 9-digit zip codes thus: 01234 12345 23456 56789 12000-2614 45623-4512 instead of the desired: 01234 12000-2614 12345 23456 45623-4512 56789 Tim C "Earl Kiosterud" wrote: Tim, You need an alphabetic sort, but your 5-digit zip codes are numeric. You can convert them all with a helper column. Presuming the list is in A2 and down: =TEXT(A1,"00000") Copy down with Fill Handle. Now to permanently convert the originals, select all of helper column, noting which cell your selection started, Copy. Select the corresponding (first) cell in the original column. Edit - Paste special - values. You no longer need the helper column. Now sort. -- Earl Kiosterud www.smokeylake.com "Tim C" wrote: Excel 2003 When I sort a list that contains text that looks like numbers, I am NOT getting the pop-up asking if whether I want them to sort as numbers or text. It just automatically sorts text that looks like numbers as numbers, which is not what I want. Any ideas? Thanks, Tim C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting order - text and numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |