Cannot sort data

[Bill]

Excel 2007

I have two columns of data. Column A is a text field and column B is a
number field. I want to sort the data, first by column A and then by column
B. When I use the Sort screen, the Order item for column A (text field) gives
the option A to Z or Z to A, which is correct. The Order item for column B
(numbers field) also gives the option A to Z or Z to A. This is incorrect. It
should give smallest to largest or largest to smallest.

I do have the option to go into the Custom List in the Order item and add
smallest to largest or larget to smallest. I have tried this but the sort in
colmn B does not come out correct.

Thinking the worksheet may be corupt, I copied columns A and B to another
worksheet and I am experiencing the same problem.

Any suggestions.

Thank you,

Bill

 

[Gord Dibben]

Are your number really numbers?

In an adjacent column enter =ISNUMBER(B1)

Copy down.

TRUE or FALSE?

If FALSE, format to General or Number then copy an empty cell and paste
special>add>ok>esc over your numbers.


Gord Dibben MS Excel MVP

 

[Bill]

Gord:

I tired your suggestion as follows:

Test in adjacent cell if true or false
I got False
Formatted my number to Number
Copied an empty cell and paste special/add/okay


I then ran the True or False test again in an adjacent cell.

I got False again.

Any other thoughts?

Thanks,

Bill

 

[Gord Dibben]

Was the copied cell really empty and was it formatted to General?

Might have a space in it.

Also your numbers could have a non-breaking space(html) which resists the
paste special>add and TRIM and CLEAN

Select the numbers and Edit>Replace

What: Alt + 0160(on the numpad)

With: nothing

Replace all.


Gord

 

[Bill]

Gord:

It worked.

Thanks,

Bill

Was the copied cell really empty and was it formatted to General?

Might have a space in it.

Also your numbers could have a non-breaking space(html) which resists the
paste special>add and TRIM and CLEAN

Select the numbers and Edit>Replace

What: Alt + 0160(on the numpad)

With: nothing

Replace all.


Gord







.

 

[Gord Dibben]

Good to hear.


Gord

Gord:

It worked.

Thanks,

Bill

 

[Gagan]

Are your number really numbers?

In an adjacent column enter  =ISNUMBER(B1)

Copy down.

TRUE or FALSE?

If FALSE, format to General  or Number then copy an empty cell and paste
special>add>ok>esc over your numbers.

Gord Dibben  MS Excel MVP

Thanks Gord,
It was quite helpful to me.

Gagan