Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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?
0
Jeremy-M
Asked:
Jeremy-M
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
It thinks it's a date. (12-01-1950)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
NorieVBA ExpertCommented:
You can prevent Excel treating the values as dates if you format the cells as text before you put data in them.
0
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
I have tried formatting the cell as TEXT
Before or after you entered DEC-50?
0
 
Martin LissOlder than dirtCommented:
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
 
FlysterCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Jeremy-MAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
Thanks.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now