Sorting 10 after 9

Question

I have looked through numerous forums and I can't find an answer to my question that works.

I need to sort a column of numbers and text in Excel on a Mac version 2011.

My column, in general, text and number formatting sorts like this:

1.1 abcde

1.2 abcde

10.1 abcde

2.1 abcde

I want it to sort like this:

1.1 abcde

1.2 abcde

2.1 abcde

10.1 abcde

I have used the work around of inserting a "0" before the single numbers such as "01.1" in the past, but my column has a buckle load of numbers and text and I just can't believe how long this has been an issue (from searching the forums) and that it still doesn't have an easy answer.

Answer 1

Your problem is that contrary to what you might think, you do not have "a column of numbers & text" :-) What you have is a column of text which contains alphanumeric data.

The most efficient [and easiest] way to be able to sort in the manner you wish would be to store the numeric nomenclature in 1 column with the textual descriptive data in the adjacent column.

I would imagine that another responder may be able to offer additional options, but there is no built-in Sort capability that will treat the numerical text as values.

Answer 2

Thank you! Creating the numbers in a separate column works. I honestly searched for about an hour before posting. I hope your answer stays up in the queue b/c this has been frustrating me for quite some time, and this is a simple fix.