Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home



Won't Sort

Posted by junsport on July 11, 2001 7:16 AM
I'm trying to sort a list by date but not all of the list is properly sorted. Some of the dated are not sorted and are pushed to the bottom. They are all in the same format and i have tried copying and pasting just the values. What can i do to fix this?????

Re: Won't Sort

Posted by Mark W. on July 11, 2001 7:44 AM
Are they all the same data type -- text vs. numeric?
Expand the column width. Are some of the dates
left-aligned while others are right-aligned?


Re: Won't Sort

Posted by junsport on July 11, 2001 7:52 AM
All the text is the same type and they are all aligned to the left.

Re: Won't Sort

Posted by Mark W. on July 11, 2001 7:58 AM
> All the text is the same type...

How have you arrived at this conclusion? Did you
test it using the ISTEXT() function?

> ...they are all aligned to the left.

What horizontal alignment are you using -- General?
Use the Format | Cells... menu command to check?


Re: Won't Sort

Posted by junsport on July 11, 2001 8:29 AM
i did the ISTEXT() function and it is false for some and true for others. I guess that some of it is in test form then. Hoe can i Change it all to the same numeric format.


Re: Won't Sort

Posted by Mark W. on July 11, 2001 8:37 AM
Perhaps, the fastest method is to use the
Data | Text to Columns... menu command, and that
presumes that an apostrophe (') prefix hasn't been
used. Are you familiar with this menu command?


Re: Won't Sort

Posted by junsport on July 11, 2001 8:43 AM
I am familiar witht the command but i dont see how it will convert the data into numeric format. All the data are numbers but the istext function noted some were true.

Re: Won't Sort

Posted by Mark W. on July 11, 2001 8:55 AM
When it parses the text representation of your dates
it will automatically recognize them as date values
and change their data types to numeric. It's magic!
Give it a try! : )


Re: Won't Sort

Posted by junsport on July 11, 2001 9:22 AM
Its still not working but thanks anyway.

Re: Won't Sort

Posted by junsport on July 11, 2001 9:31 AM

Re: Won't Sort

Posted by Mark W. on July 11, 2001 10:24 AM
Does it work if you manually edit one of these
dates? Have you thrown in the towel?


Re: Won't Sort

Posted by junsport on July 11, 2001 11:32 AM
Yes it works if i type them in manually. I guess i have to type it in.

Re: Won't Sort

Posted by Mark W. on July 11, 2001 11:51 AM
No. To me that means the Text to Columns... approach
should have worked. Did you not select the column
containing the text representations of date values,
and then press the [ Finish ] button at Step 1 of 3
on the Text to Column Wizard? Can you provide me
an example of one of these dates?


Re: Won't Sort

Posted by Sam on July 11, 2001 8:42 PM
Mark,

I have the same problem, but I'm trying to create a conditional sum formula and it won't recognize the values because they are text(exported from Access I think).

I'm gonna try your approach.


Re: Won't Sort

Posted by Mark W. on July 12, 2001 5:58 AM
Sam, you can also coerce a text representation of
an numeric value by adding 0 to it. For example,

="1"+0 produces 1
="1/1/2001"+0 produces 36892 (the datevalue for Jan 1)




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.