Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
I tried converting the text "Fri Mar 11" to date and sorting the column. Didn't work –  iceman May 16 '11 at 4:55
    
then your conversion isn't right, Excel will sort date serial numbers. How did you do the conversion? –  chris neilsen May 18 '11 at 6:34

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.