• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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