Why Excel cannot sort a column with mixed letters and dates?

I make some codes like the following and sort it in an A-Z order, the result is as follows:

Allarts: China>Hong Kong>1/3/2013>a
Allarts: China>Hong Kong>1/6/2013>a
Allarts: China>Hong Kong>1/8/2015>a
Allarts: China>Hong Kong>10/10/2015>a
Allarts: China>Hong Kong>10/10/2015>b
Allarts: China>Hong Kong>10/8/2013>a
Allarts: China>Hong Kong>11/5/2013>a
Allarts: China>Hong Kong>11/7/2015>a

Apparently the sorting gets stuck on the date part (2015 float above 2013, or 11/5/2013 floats above 11/7/2013). I have already sought some general help from the Microsoft team. They taught me to change the format of that column to "value" and sort it again. They also said the Excel should be able to identify x/xx/xxxx as the dates and hence sort them from old to new as well, after sorting the text parts. I tried their advice but I still failed.

Can anyone tell me what I should do to make the Excel able to sort the text with consideration to the chronological order of the dates?

Hi,

I guess you have this text: Allarts: China> Hong Kong> 1/3/2013> a, intro cell and not many, and excel sees that text as it is (what it is).
If this is the case then the sorting is good because the excel sorts after the first letter, then after the 2nd, then the 3rd ... if the letters are the same.

if you want to sort by date, use TextToColumn with the separator ">", and here sets the data column to be seen as the date, and then selects the date column and sorts.

The two methods above are to break each code into two parts i.e. the text and the dates, into two different columns, then sort the text and then the date.

My problem is, i put the whole code into one cell (as I do not want to sort two times to complete the sorting) and I expect the Excel is able to identify the text and then the date even within one cell. The Microsoft Team guy says the Excel should be able to read x/xx/xxxx as the date and hence will be able to adjust its sorting criteria to "old to new (date)" after the text part, all at once, in one cell (unless I misunderstood him).

Anyone has further insight about the above?

Anyway to achieve what I want (a single sorting) if the whole code is put in a single cell?

Does that mean I should really break it down into the text part and the date part and hence sort two times?

You cannot sort it in-place but you can get result like you want.
Use PowerQuery aka Get&Transform split it by > then sort and then merge columns and result load to the sheet back

I think you can use TEXT TO COLUMNS first, but make sure you set up > as the delimiter, then move across to the date column, and set the date to DMY. If you don't do this, excel gets confused about dates that are not in the MMDDYYYYY format used in the USA.

You can then sort in the usual way using the date column.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".