Sorting mixed data

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

From Excel 2000 Help....
-------------------
Check that mixed data is formatted as text If the column you want to
sort contains both numbers and numbers that include text characters
(such as 100, 100a, 200, 200a), you need to format them all as text. If
you do not, the numbers will be sorted first, then the numbers that
include text will be sorted. To format a number as text, click Cells on
the Format menu, click the Number tab, and then click Text in the
Category list. To type a number as text when you are entering new data,
format the cell as text before you begin typing.
---------------------

This is exactly the problem I have. The column has digits and digits
with text as 16, 16b, 17, 17b.
I highlighted all the cells and formatted them as text. I've checked
dozens of them and the formatting is correct.
But then I sort on that column, I don't get the correct results.
I get 16, 17, 18,19, 16b, 17b, etc.
I need 16, 16b, 17, 17b, etc.
Any ideas?
Jim
 
No good advice from MS: formatting the cells afterwards does not make them
text, as you can easily check with ISNUMBER()
You could add a space to force it to text: =" "&A1. Don't forget to Copy,
Paste Special values. Then sort the text area.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
You don't even need that space character:

=""&a1
will be text.

And depending on the number of digits, the OP could use:
=text(a1,"00")
 
Back
Top