Sorting cells with letters and numbers

[tpepere]

I'm trying to sort a substantial list of titles e.g. C1, C2, C3, C4..
C10, C11, C12, C100 etc. However on sorting Excel arranges the cell
C1, C10, C11, C12, C100, C2, C3, C4 (default sort order). Is there
way to sort the cells paying attention to the numeric value e.g. C
then C2, not C1 then C100?

Thanks

 

[David McRitchie]

You would need a helper column to sort. Besides say you
had something with two sets of numbers like Color1style34,
so trying to do something generically is not cut and dried.

Take a look at
Sorting, Some notes on sorting in Excel
http://www.mvps.org/dmcritchie/excel/sorting.htm#pcdigits

 

[RagDyeR]

If all of your sorting is to include just data as simple as your example,
you could very easily create a custom list, containing your examples, and
then sort using the custom list as the key.

For example, using the scenario in your question, enter
C1
in A1
and
C2
in A2
Select *both* cells and drag down to copy as far as you anticipate that the
length of your list might be.

Then, while this list is *still* selected,
<Tools> <Options> <CustomLists> tab,
Click "Import", then <OK>.

Now, when you wish to sort your "C" list,
Select the cells to sort, then:
<Data> <Sort> <Options>,
And expand the "FirstKeySortOrder" box,
And then click on your custom list,
Then <OK> <OK>.

You can create numerous custom lists to use as sort keys.

Also, the first thing I do when I sit down at a new machine with XL, is
create a custom list of the alphabet.
Besides sorting, it enables click and drag auto expansion.
Don't know why Redmond didn't include this as a default, together with days
and months!?!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I'm trying to sort a substantial list of titles e.g. C1, C2, C3, C4...
C10, C11, C12, C100 etc. However on sorting Excel arranges the cells
C1, C10, C11, C12, C100, C2, C3, C4 (default sort order). Is there a
way to sort the cells paying attention to the numeric value e.g. C1
then C2, not C1 then C100?

Thanks.