Is this a bug? Numbers as text sort…

[evert]

In one of my sheets I have column A defined as Text format. Then I type in numbers:
34
22
58
Then I sort column A, and then the numbers are sorted fine.

I have another helpsheet with numbers:
12
13
88
The cell format in this helpsheet is General.

I paste these numbers into column A of the first sheet, using the options Paste Special -> values.
So now in the first sheet column A we have:
22
34
58
12
13
88

Then I sort by column A, resulting into:
12
13
88
22
34
58

This is not good since 88 was supposed to end up below.

Excel somehow remembered the original number format of 12, 13 and 88 when I pasted specially as values. I know when I would have typed 12, 13 and 88 directly, the sorting would have had the expected result.
Is this a bug?
I'm using Excel 2000.

 

[Jonmo1]

It's because the first set of numbers (12,13,88) are not really numbers. They are TEXT strings that look like numbers (Numbers Stored As Text).

Text and numbers are sorted differently. When choosing Ascending, Text comes first, numbers last.


Try this in an adjescent column

=A1*1

And fill down to the end of the data.

Now that column will be all numbers, and you can sort by that column.

 

[Jonmo1]

OK, I said that backwards.
34,22,58 are the numbers stored as text
and the actual numbers 12,13,88 are sorted first when sorting ascending...

 

[evert]

Still I would have expected that pasting as VALUE would have turned the numbes also into TEXT strings that look like numbers

 

[Jonmo1]

Paste special values pastes exactly what was copied.

If text was copied (even numbers stored as text), it's pasted as text.
If numbers were copied, it's pasted as numbers.

You can try using Data - Text to columns to convert the "Numbers Stored As Text" to actual numbers.