I have an excel spreadsheet with 1 column. each row has the following information in a single cell:

USER=GRANITE.BALLS DC=BLAHBLAH ETC=INSERT 1/21/2011 7:58 AM



how can I sort this single column by date?

There are 800+ lines, so manually doing this is not a pretty option. Surely Excel can do this for me, I'm just an idiot with functions.

1/24/2011 6:55:33 PM

Not sure the complexity of the cases. But check out "text to columns"

1/24/2011 7:14:07 PM

Might be easiest to use the Text to Columns feature.

In 2007 or higher go to the data tab on the ribbon and select the Text to Columns, then follow the instructions and set "Space" as the delimiter, then everything separated by a space will be split into its own column, this will screw up the timestamp into a date and a time but its pretty easy.


ps whoever put that into a single column is an asshole

1/24/2011 7:14:19 PM

text to columns +1

1/24/2011 7:16:19 PM

lol 12 seconds off

1/24/2011 7:16:55 PM

try maybe the Text To Columns feature.

1/24/2011 7:24:17 PM

Another thing to try if the previous suggestions don't work is the Text to Column feature.

1/24/2011 7:34:02 PM

im fucking around with a feature i found called "text to columns" and it keeps turning my goddamn dates into #'s.



I'll update once i conquer excel.


Here's a direct example of the clusterfuck I'm dealing with:

CN=GRANITE BALLS,CN=Users,DC=TWW,DC=local;1/18/2011 9:14:57 AM

[Edited on January 24, 2011 at 7:42 PM. Reason : k]

1/24/2011 7:39:06 PM

done.


had to run that shit twice.

1/24/2011 7:46:34 PM

Next problem:


I have 4 columns:

CN=GRANITE BALLS,CN=Users,DC=TWW,DC=local 1/18/2011 9:14:57 AM


Some of the items in B column are not dates, just text ("NEVER"). how can I sort my shit by date, and just seperate all the fucking "NEVER"s.

[Edited on January 24, 2011 at 7:54 PM. Reason : lol, triple post]

[Edited on January 24, 2011 at 7:59 PM. Reason : Got it. I had to copy/paste special, select all, sort by column, JAN/FEB/MAR/APRIL]

1/24/2011 7:54:20 PM

You need to have just the date data in its own column, and it needs to be in date format, not text, and just sort it. Excel will put all the "never" shit together either before or after the dates.

1/24/2011 8:01:06 PM

I feel like a master of machines today.

1/24/2011 8:10:59 PM

if you didn't want to sort anything (if its in an important order) you can filter the table so that anything that says "never" doesn't show

[Edited on January 24, 2011 at 8:53 PM. Reason : for future reference as you continue on your mastering ]

1/24/2011 8:52:42 PM

Quote :

"I feel like a master of machines today."

Do masters frantically ask the interwebs for advice, following it blindly for answers a simple 5 minute search of Office Help would yield? [/flame]

1/24/2011 9:02:57 PM

I spent at least 6 minutes searching before i threw in the TWW towel.

Excel is hard, bro.

1/24/2011 9:17:29 PM

You could try Text to Columns.

1/24/2011 9:25:14 PM

I just rofled on my keyboard.


jerk.

1/24/2011 9:27:39 PM

1/24/2011 10:06:01 PM

I see that Granite Balls figured out how to sort his data

1/25/2011 8:37:40 PM

Goddamnit.



That's confidential information.



Paging qntmfred

[Edited on January 25, 2011 at 8:45 PM. Reason : k]

1/25/2011 8:44:59 PM

Quote :

"Do masters frantically ask the interwebs for advice, following it blindly for answers a simple 5 minute search of Office Help would yield? "

They most likely do.

The best masters are the best googlers...

1/25/2011 8:45:26 PM

could someone point me to a good advanced excel tutorial?

2/1/2011 9:42:24 PM

Is there an easy way to merge multiple files into one workbook? I'm trying to take the first sheet from a list of workbooks and create one workbook with multiple sheets with them. Is there an easy way to do this, or will I need to write something? Merge doesn't seem to be what I need.

2/4/2011 4:14:18 PM

Anyone have any idea? I've got something working but i'm still curious if there is a better way to do it.

2/7/2011 10:01:23 AM

if you use the advanced text to columns feature you can also specify the column format for each section. careful though. this is advanced feature

2/7/2011 10:13:26 AM

I would have done a macro and be done with it. Pretty easy to grab a listing of all the files in a directory, parse the ones with .xls, then just create a new worksheet in the target workbook and copypasta every sheet 1 in the workbooks you are opening.

You could probably do it in less than 50 lines of code. Probably more like 25.

2/7/2011 12:23:22 PM

thats basically what i did, i was just curious if there was a feature built in that would have done it for me.

2/7/2011 1:22:21 PM