string manipulation

[happyPotter]

Hello

I have a large amount of records and would like to sort the records by
date Year-Month-Day

In cell C3, i have

Apr 1 2002 7:23AM

.....there are 2 spaces between Apr and 1. There's also 2 spaces
between 2002 and 7:23AM

I have tried to use the Sort option in the data toolbar as well
changing the format of cell C3 to date and custom.

Would I have to send use string manipulation function or formula? In
cell D3, how would I to use Left function and get rid of the 2 spaces
between the month and day. I need to sort by Year then by Month, then
by day

 

[Alex Delamain]

It is possible to write a formula to turn your date into a format exce
will recognise. Can you tell me what happens when the date is the 10t
or above - are there still two spaces between Apr and the day

 

[Frank Kabel]

Hi
in B1 try
=DATEVALUE(TRIM(MID(A1,FIND(" ",A1)+2,2)) & "-" & LEFT(A1,FIND("
",A1)-1) &"-" & MID(A1,FIND(" ",A1,6)+1,4))
format as date and sort with this column

 

[Ron Rosenfeld]

Hello

I have a large amount of records and would like to sort the records by
date Year-Month-Day

In cell C3, i have

Apr 1 2002 7:23AM

....there are 2 spaces between Apr and 1. There's also 2 spaces
between 2002 and 7:23AM

I have tried to use the Sort option in the data toolbar as well
changing the format of cell C3 to date and custom.

Would I have to send use string manipulation function or formula? In
cell D3, how would I to use Left function and get rid of the 2 spaces
between the month and day. I need to sort by Year then by Month, then
by day

If I understand you correctly, you have a column of dates and times in the
format described above, which you would like to sort in true chronological
order.

You need a helper column in which you have transformed your string into an
Excel date/time construct. Excel stores dates as sequential integer numbers;
and times as fractions of a day.

The formula below will convert your string into an excel date/time. If you
format it as a date time, you should see the identity.

Put this formula into the helper column; then sort on that column:

=SUBSTITUTE(TRIM(LEFT(C3,LEN(C3)-2))," ",", ",2)+0.5*(RIGHT(C3,2)="PM")




--ron