Sorting Mixed Data Marked as Text

Question

Even if you indicate that data in a cell is text, Excel takes any value in that looks like a number and sorts it separately from the rest of the data. Sometimes it gives you a choice via the pop up, but the two choices have the same effect, they sort the numbers as numbers and the text as text, separately. This means you can not sort a list that contains data like 7.1, 7.1.1, 7.2, 7.2.1, 7.2.1.4. In other words,things like software versions, sections of numbered documents, etc. This is one of the worst examples of software companies doing your thinking for you and assuming every user and application is the same. Anyone know how to get around this ?

Answer 1

Make sure that all values that can be interpreted as numbers, such as 7, 7.1, 7.2 etc. are preceded by an apostrophe.

When you sort the data, Excel will put up a warning dialog.

Select the option "Sort numbers and numbers stored as text separately", then click OK.

Answer 2

Thanks for the reply. Actually, the dialog box did not come up the first few times I sorted, but then after playing around with it, the dialog box did appear and then I was able to sort the way I wanted.