Solved

Excel Sort "DEC" Anomaly

Posted on 2013-01-05
20
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
20 Comments
 
LVL 47

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 47

Expert Comment

by:Martin Liss
ID: 38746874
It thinks it's a date. (12-01-1950)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

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 47

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 34

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 47

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
 
LVL 47

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 47

Expert Comment

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

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 47

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 47

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 47

Expert Comment

by:Martin Liss
ID: 38802061
Thanks.

Marty - MVP 2009 to 2012
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

734 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