Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
bug in excel 2007 for sorting function?
Hi, I believe there is a bug in excel 2007 for sorting function. I have a
worksheet with the first column being a building code. Some of the codes consist of a number only(such as 1001), and some with a number and a letter(such as 1001a). my intent was to be able to sort these codes numerically, such as 1001, 1001a, 2000, etc. I painted down the column and did a format, format cells, text. Then tried to sort. excel put the numbers with letters after all the pure numbers, even though I attempted to format all the cells as text. I was able to find a work around: I inserted 2 columns to the left of my building code column. In the first column, I inserted the formula "=type(C1)". this column returned some type 1's (number) and some type 2's (text) even after I had painted down the column and formatted all the cells as text. Then I painted the building code column down and formated as number, then input "=text(C1,0)" in the 2nd column, which forced all the building codes to be type text. Then I was able to sort numerically. The point is that excel should have made all the building codes text when I painted down the column and did a format, format cells, text, but it didn't. Have you had other reports of this problem? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
bug in excel 2007 for sorting function?
Changing the *display* format of a cell from number to text does not affect
the content. If it was previously a number it will still be a number. You can check with ISNUMBER and ISTEXT. If you want to enter a number as text you need to format the cell as text *before* you type in the number. If you want to change your existing numbers to text, try =C1&"" -- David Biddulph "javamama" wrote in message ... Hi, I believe there is a bug in excel 2007 for sorting function. I have a worksheet with the first column being a building code. Some of the codes consist of a number only(such as 1001), and some with a number and a letter(such as 1001a). my intent was to be able to sort these codes numerically, such as 1001, 1001a, 2000, etc. I painted down the column and did a format, format cells, text. Then tried to sort. excel put the numbers with letters after all the pure numbers, even though I attempted to format all the cells as text. I was able to find a work around: I inserted 2 columns to the left of my building code column. In the first column, I inserted the formula "=type(C1)". this column returned some type 1's (number) and some type 2's (text) even after I had painted down the column and formatted all the cells as text. Then I painted the building code column down and formated as number, then input "=text(C1,0)" in the 2nd column, which forced all the building codes to be type text. Then I was able to sort numerically. The point is that excel should have made all the building codes text when I painted down the column and did a format, format cells, text, but it didn't. Have you had other reports of this problem? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
bug in excel 2007 for sorting function?
On Thu, 23 Apr 2009 04:13:01 -0700, javamama
wrote: Hi, I believe there is a bug in excel 2007 for sorting function. I have a worksheet with the first column being a building code. Some of the codes consist of a number only(such as 1001), and some with a number and a letter(such as 1001a). my intent was to be able to sort these codes numerically, such as 1001, 1001a, 2000, etc. I painted down the column and did a format, format cells, text. Then tried to sort. excel put the numbers with letters after all the pure numbers, even though I attempted to format all the cells as text. I was able to find a work around: I inserted 2 columns to the left of my building code column. In the first column, I inserted the formula "=type(C1)". this column returned some type 1's (number) and some type 2's (text) even after I had painted down the column and formatted all the cells as text. Then I painted the building code column down and formated as number, then input "=text(C1,0)" in the 2nd column, which forced all the building codes to be type text. Then I was able to sort numerically. The point is that excel should have made all the building codes text when I painted down the column and did a format, format cells, text, but it didn't. Have you had other reports of this problem? I am assuming you changed the cell format after entering the data. Changing the format of a cell does not necessarily change the type of data already stored within the cell. Formatting as text will ensure that any NEW data entered into the cell will take on the Text attribute, however. One way to convert already entered data to TEXT, so it will sort as you wish, is with a VBA macro. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ====================================== Option Explicit Sub NumToText() Dim c As Range For Each c In Selection With c .NumberFormat = "@" .Value = .Text End With Next c End Sub ========================== --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
bug in excel 2007 for sorting function?
On Thu, 23 Apr 2009 08:34:33 -0400, Ron Rosenfeld
wrote: On Thu, 23 Apr 2009 04:13:01 -0700, javamama wrote: Hi, I believe there is a bug in excel 2007 for sorting function. I have a worksheet with the first column being a building code. Some of the codes consist of a number only(such as 1001), and some with a number and a letter(such as 1001a). my intent was to be able to sort these codes numerically, such as 1001, 1001a, 2000, etc. I painted down the column and did a format, format cells, text. Then tried to sort. excel put the numbers with letters after all the pure numbers, even though I attempted to format all the cells as text. I was able to find a work around: I inserted 2 columns to the left of my building code column. In the first column, I inserted the formula "=type(C1)". this column returned some type 1's (number) and some type 2's (text) even after I had painted down the column and formatted all the cells as text. Then I painted the building code column down and formated as number, then input "=text(C1,0)" in the 2nd column, which forced all the building codes to be type text. Then I was able to sort numerically. The point is that excel should have made all the building codes text when I painted down the column and did a format, format cells, text, but it didn't. Have you had other reports of this problem? I am assuming you changed the cell format after entering the data. Changing the format of a cell does not necessarily change the type of data already stored within the cell. Formatting as text will ensure that any NEW data entered into the cell will take on the Text attribute, however. One way to convert already entered data to TEXT, so it will sort as you wish, is with a VBA macro. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ====================================== Option Explicit Sub NumToText() Dim c As Range For Each c In Selection With c .NumberFormat = "@" .Value = .Text End With Next c End Sub ========================== --ron I neglected to mention that you must select the range of cells to convert, **BEFORE** starting the Macro dialog box. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting in Excel 2007 | New Users to Excel | |||
Excel 2007 - Sorting | Excel Worksheet Functions | |||
Repeat function in Excel 2007 not working when sorting data | Excel Discussion (Misc queries) | |||
Sorting in Excel 2007 | Excel Discussion (Misc queries) | |||
Sorting function in Excel 2003 lost in the 2007 version | Excel Worksheet Functions |