Re-ordering data in a cell in spreadsheet

Question

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?

Answer 1

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.

Answer 2

The easiest way is to open the VBA editor Alt+F11, Insert a new module and enter this UDF

Function SplitDate(ByVal strTemp As String, _
ByVal strDelimiter As String, _
ByVal lngElement As Long)

SplitDate = Split(strTemp, strDelimiter)(lngElement)

End Function

Then back in Excel you can use the UDF formula as =SplitDate(INPUTTEXT,DELIMITER,ELEMENTTORETURN)

Eg, say you have text Fri Mar 11 in cell A1 then

=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.

Alternatively use =DATE(YEAR(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))),MONTH(RIGHT(A1,LEN(A1)-FIND(" ",A1,1))),1) to convert the text in cell A1 into a date. Since there is no DAY I've assumed 1st of the month (Change the last ,1 to alter the DAY).

Answer 3

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

=DATEVALUE(RIGHT(A1,LEN(A1)-8)&" "&MID(A1,5,3)&" 2011")

So you could add an extra column and paste that formula in, copy it down and sort on that column.