bug in excel 2007 for sorting function?

[javamama]

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/com...dg=microsoft.public.excel.worksheet.functions

 

[David Biddulph]

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&""

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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