I have an Excel spreadsheet where data is time stamped, but the time stamp is as follows (in one cell):
Fri Mar 11
If it was 11 Mar Fri, the data would be easier to sort. How do I change the values from Fri Mar 11 to 11 Fri Mar?
If you store it as a date/time stamp, it shouldn't matter how it is formatted - simply do a sort by column.
If however it is just plain text, quite frankly, it is much harder.
I would possibly recommend just reordering it yourself.
Alternatively, you can change the column to a date/time, I think Excel should understand and convert it fine.
The easiest way is to open the VBA editor Alt+F11, Insert a new module and enter this UDF
Then back in Excel you can use the UDF formula as =SplitDate(INPUTTEXT,DELIMITER,ELEMENTTORETURN)
Eg, say you have text
=SplitDate(A1," ",0) will return "Fri"
=SplitDate(A1," ",1) will return "Mar"
=SplitDate(A1," ",2) will return "11"
You can then combine these into a formula to rearrange your string.
Assuming that the format is always 3 letters for day, 1 space, 3 letters for month, 1 space, then 1 or 2 digits for day of month, and all your dates are in 2011, the text can be converted into a number representing the date with the formula
So you could add an extra column and paste that formula in, copy it down and sort on that column.