Link to home
Start Free TrialLog in
Avatar of Jeremy-M
Jeremy-M

asked on

Excel Sort "DEC" Anomaly

When I perform a sort on a column in Excel if the first 3 characters of a cell are "DEC" that cell will always be the first cell in the column after the file is sorted. Why?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

How are you doing the sorting because it doesn't happen to me

This list

ghg
decss
DECaa
aaa
bbb

sorted just fine to

aaa
bbb
DECaa
decss
ghg
Avatar of Jeremy-M
Jeremy-M

ASKER

All cells are formatted as text cells the exact phrase is "DEC-50"; every time I sort this column of items all items beginning with "DEC-50" are at the top of the column.  Other items beginning with "DEC-100", "DEC-201", etc. are sorted correctly.
It thinks it's a date. (12-01-1950)
Try this macro which sorts column A.


Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

Martin,

I know Excel thinks contents of the cell are a date.  I need to know why Excel sees "DEC-50" as "12-01-1950"?  

I appreciate the effort in your macro but the issue is not how can the column be sorted correctly. I solved that problem by sorting the SQL file prior to exporting.

Thanks,

Jeremy
Because it thinks the DEC is December and it thinks 50 is the year. I don't know how to explain it any better but perhaps you'll find what you need in this article.
You can prevent Excel treating the values as dates if you format the cells as text before you put data in them.
Imnorie,  

I tried setting the formatting all cells in the column as text but that did not solve the problem.

Martin,

Thanks for trying but I need an explanation for why "DEC-50" is sorted as "12-01-1950."

Jeremy
One of us is missing something so let me try again. Please don't be offended by the following.

Do you understand that Excel thinks Dec-50 is a date?
Do you understand that mm-dd-yyyy is apparently the standard date format?
Do you understand that in sorting ascending, numbers always appear before letters?

And so 12 appears before xyz.
Yes to all 3 questions.

I'm not offended but the question is why does Excel interpret "DEC-50" as "12-01-1950" when Sorting a range of cells even if the format of the cell is TEXT and not as "DEC-50" and  place the cell after "DEC" and before "DEC-5a?
I think that as imnorie suggested if you format the cell/column before adding DEC-50 it knows  it's text so it stores "DEC-50". However if you add it while the cell is General, it says "ah-ha" (or maybe "ha-ha") it's a date and it stores the numeric equivalent as described in the article pointed to above.
In my comment prior to your most recent comment I stated that I have tried formatting the cell as TEXT; however,  the cell was still sorted as "12-01-1950"
I have tried formatting the cell as TEXT
Before or after you entered DEC-50?
The attached WB has two columns. Col A was formatted as Text prior to adding data and the other was left alone. I added the same data to each column and then sorted each column separately (non-expanded). It sorted the headings in with the data but you can ignore that.
Book3.xlsx
Try inserting a new column. Format it as text. Copy your current column and paste special - values. I have found that once a cell thinks it's a date, you have to empty it first before you can reformat it.

Flyster
once a cell thinks it's a date, you have to empty it first before you can reformat it
Yes that's because Excel has stored it as a number. For example 9-Jan-2000 is stored internally as 36544.
There are other cells beginning with "DEC", such as, "DEC-12", "DEC-25", "DEC-100", "DEC-200", "DEC-201", "DEC-301", etc. that are not sorted as dates. Why is this cell seen as a date?

J
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Martin,

I awarding you the points for trying but you have answered the question I asked, "Way does Excel sort "DEC-50" as a date when exported column is text?"

Jeremy
Thanks.

Marty - MVP 2009 to 2012