Solved

Excel Sort "DEC" Anomaly

Posted on 2013-01-05
20
164 Views
Last Modified: 2013-01-21
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?
0
Comment
Question by:Jeremy-M
20 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38746861
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
0
 

Author Comment

by:Jeremy-M
ID: 38746872
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38746874
It thinks it's a date. (12-01-1950)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38746886
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

0
 

Author Comment

by:Jeremy-M
ID: 38746934
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38746949
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 38747037
You can prevent Excel treating the values as dates if you format the cells as text before you put data in them.
0
 

Author Comment

by:Jeremy-M
ID: 38747087
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38747192
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.
0
 

Author Comment

by:Jeremy-M
ID: 38747241
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?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 38747261
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.
0
 

Author Comment

by:Jeremy-M
ID: 38747322
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"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38747333
I have tried formatting the cell as TEXT
Before or after you entered DEC-50?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38747350
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
0
 
LVL 22

Expert Comment

by:Flyster
ID: 38747391
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38747397
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.
0
 

Author Comment

by:Jeremy-M
ID: 38801601
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
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38801726
I can't explain the first two because in my understanding that 00 to 29 should be treated as as years 2000 - 2029. The other numbers are not treated as years because they are > 99 and Excel only interprets numbers as dates if they would fall in this millennium.
0
 

Author Closing Comment

by:Jeremy-M
ID: 38802030
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38802061
Thanks.

Marty - MVP 2009 to 2012
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now