Unusual Custom Cell Format Needed to Correctly Sort Values

**momus44** · 08-18-2010, 04:43 PM

Hi,

I have a spreadsheet that contains a column with values. These values can be alphanumeric, but often just have numbers. Also, some have leading zeros. An example of the column would be:

080
080A
085V
086
128
119
119C
016
143D
143B
144

I want it to sort first numerically and then alphabetically, and I need to keep the leading zeros present. I've tried it set as text, but the results are inconsistent. For instance 080 will show up before 016. Setting it to just be a number drops the leading zeros. I know how to maintain the zeros through custom formatting, but the list still does not sort correctly.

Any advice on what custom format would correct this would be great, as would pointers to anything else I should be looking at.

Thanks very much for your time, and please let me know if you have any questions.

**martindwilson** · 08-18-2010, 04:51 PM

given your example what would the final sort look like? post a workbook with some real examples!

**shg** · 08-18-2010, 05:10 PM

Formatting does not affect the underlying cell values, and so does not affect the sort order.

**momus44** · 08-18-2010, 05:47 PM

Ok, I've attached a workbook with the values I outlined above. The desired sort results would look like:

016
080
080A
085V
086
119
119C
128
143B
143D
144

Essentially sorting on the numeric value first then the alpha value, though the numeric value is the most important.

Thanks.

**shg** · 08-18-2010, 06:30 PM

In your example, in B1 and copy down,

=A1 & ""

... then sort the ensemble by col B. When prompted, tick the option to Sort numbers and numbers stored as text separately

**momus44** · 08-18-2010, 07:10 PM

That worked! Thanks so much! I had to pay more attention to the sorting options, but that worked perfectly when I more carefully followed instructions.

Again, thanks!