General discussion

Locked

Sort Excel data with leading zero's

By Blackcurrant ·
Hi

I have an Excel 2000 workbook that has a sheet with a list of numbers in column A. The numbers are up to 4 digits in length. In our work, numbers less than 1000 are prefixed by 1 or 2 zeros and are presented as 001, 009, 010, 035, 0123, 0526, 0999, 1025 etc.

I currently have a list of these numbers which I am trying to sort however, when I sort them I get these results:

1909
1913
2104
0104
0104
0501
0502
0903
0903
0903
0907
0908
1104
1104

Which makes no sense to me!

The table contains other data arranged in columns and each column has its own heading. I have tried various formats for these cells, including custom formats "0000" and "0#". I sort the data by column A.

Does anyone know how I may successfully sort this data so that it is in numerical order?

Thanking you in advance...

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by softcorp.us In reply to Sort Excel data with lead ...

Hello BC...

Your sheet's odd behavior is caused by mixing numeric and string values. String values are those that start with a single quote.

The first three values do not have the single quote and are thus numeric. The remaining values do have the single quote and are thus strings. The numerics sort first, in ascending order. The strings sort second in ascending order.

To fix it: Consistenly use only numerics or strings and you will get the correct result.

Bad Excel! Bad! :-)

-----Steve Jackson

CEO/CSA
Software Corporation (Softcorp)
http://www.softcorp.us/probono
Advanced pro bono tools and utilities free for personal use

Collapse -

by softcorp.us In reply to

Something more:

In order for the strings to have leading zeros and sort in the desired ascending "numeric" order, they will all have to be the same length.

Example: Incorrect order caused by leading zeroes and differing string lengths.

00104
00105
0104
0104
0501
0502
09000
1104
1104

If you can consistently use real numeric values, you won't have to deal with these undesirable sorting issues. To get leading zeroes automatically on numeric values, you can use a custom number format of 00000 (always 5 digits, with leading zeroes if needed).

1. Right click on column.
2. Format Cells
3. Number tab.
4. Category: Custom
5. Type: 00000
6. Click OK.

If you must have varying numbers of leading zeroes for some reason, you can get the ascending "numeric" sorting by doing this:

1. Add a new column to the sheet that you will sort on.

2. Put the following formula in each cell of the new column: =CR where C = column letter of the original column and R = row number. The "original column" is the one with the varying length values. Note: I presume you know you can type this in once cell and drag it across many cells to propagate it correctly.

3. Sort on the new column. The new column will have all numeric values regardless of whether the valeus in the "original column" are character or not, with leading zeroes or not.

Collapse -

by Blackcurrant In reply to

Hi, and many thanks for answering. It turns out they were using a template they had created to enter the data where Column A was formatted as Text. Doh!

Anyway, I have changed the template so that Column A now has a custom format applied comprised of zero's. Luckily, this template was created quite recently, so there should not be too many files to edit.

Thanks again

Collapse -

by Blackcurrant In reply to Sort Excel data with lead ...

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums