Sorting Problem

[ammdumas]

I have a column which has 2 types of numbers. One set is 4 digits (i.e. 1234) and the other set has a dash (i.e. 1234-001). When I try to use the Data--Sort function it places all the dashed numbers at the bottom as opposed to sorting them by the first 4 digits ,ie.
1234
1234-001
1234-002
1235
1235-001, etc.

Instead, what is does is this;
1234
1235
1234-001
1234-002
1235-001 etc.

Formatting the cells to number text, etc. does nothing. How do I fix this?

[zzydhf]

sometimes I have trouble formatting the cells to text after they are numbers. anyway, for a short list, put a single quote (') infront of the number ('1234).
for a long list, if data is in a2, in b2 put =clean(trim(a2)) copy down. sort on column b

[Al Chara]

Why don't you do a replace, i.e. replace all "-" with ".", then sort and then replace back the "." with "-".

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-24 10:33 ]

[Mark W.]

On 2002-05-24 10:22, ammdumas wrote:
I have a column which has 2 types of numbers. One set is 4 digits (i.e. 1234) and the other set has a dash (i.e. 1234-001). When I try to use the Data--Sort function it places all the dashed numbers at the bottom as opposed to sorting them by the first 4 digits ,ie.
1234
1234-001
1234-002
1235
1235-001, etc.

Instead, what is does is this;
1234
1235
1234-001
1234-002
1235-001 etc.

Formatting the cells to number text, etc. does nothing. How do I fix this?

Actually, what you have is a column containing 2 different data types (text, as in "1234-002" and numbers, as in 1234). As stated in the Excel Help topic, "Sort a list", numbers will always be sorted (in ascending order) ahead of text values. You had the right idea of apply a Text format to all of the values, but perhaps you were unaware that Excel formatting never changes the internal representation of a value stored in a cell. So unless you re-edited the cell after the application of a Text format the value will retain its numeric data type.

Perhaps, the easiest solution to your data type conversion need is to select the cells containing your columnar data, choose the Data | Text to Columns... menu command, press [ Next> ] until you reach Step 3 of 3 of the wizard, choose the Text format option and press [ Finish ].

[ This Message was edited by: Mark W. on 2002-05-24 10:49 ]

[ammdumas]

Thx. I will try them all.