Can Excel sort alpha numeric characters?


I attached a spreadsheet that explains how I attempted to sort the cells and how I want it sorted.


Any help would be greatly appreciated.



Thank you,


Karen

Attached Files

AlphaNumericSort.xlsx (9.0 KB, 24 views)

You must have the column with alphanumeric values formatted as text. Then it'll sort exactly as you wanted.

Simply changing column format is not enough! When you format the column as text, it will behawe as all entries are texts, but really they remain numbers - you have to edit all entries to convert them really (select entry, press F2, Press Enter for every entry).

To convert all entries at one go:
Into some empty column enter formula like ="" & A2, and copy it down for all entries in column A;
Format column A as Text;
Copy the range with formulas;
Replace old entries in column A with values of copied formulas (PasteSpecial.Values);
Delete column with formulas.

Thank you so much for your help.

Now if this data is being used for a pivot table, should I sort the raw data first or should it be sorted in the pivot table?


Your help is greatly appreciated.