Sort Values

[jebindavidson]

Hi,

Please help me sort the following,

53 days 2 hrs 15 mins
29 days 2 hrs 28 mins
31 days 8 hrs 34 mins
18 days 0 hrs 19 mins
19 days 3 hrs 5 mins
11 days 4 hrs 26 mins
2 days 20 hrs 43 mins
2 days 15 hrs 15 mins
4 days 21 hrs 24 mins
1 days 19 hrs 30 mins
1 days 16 hrs 0 mins
1 days 15 hrs 49 mins
28 days 7 hrs 28 mins
6 days 7 hrs 21 mins


You're help is much appreciated.

[bebo021999]

Assuming data starts from A1, one space between words.
Try in B1 then copy down:

Please Login or Register  to view this content.

Sort by column B

Untitled.png

[sandy666]

or
B2: =IF(LEN(LEFT(A2,SEARCH(" ",A2)-1))<2,"0"&LEFT(A2,SEARCH(" ",A2)-1),LEFT(A2,SEARCH(" ",A2)-1))
and drag down
then select both and sort by B column

[bebo021999]

or
B2: =IF(LEN(LEFT(A2,SEARCH(" ",A2)-1))<2,"0"&LEFT(A2,SEARCH(" ",A2)-1),LEFT(A2,SEARCH(" ",A2)-1))
and drag down
then select both and sort by B column

@sandy:
You paid no attention to Hrs and Mins!

[sandy666]

@bebo021999
Yeah it was bad idea
I tried to delete it but you was faster so it will stay with shame on me

[joeu2004]

@jebindavidson.... I would convert them to Excel time by entering the following into an adjacent column:

=LEFT(A1,FIND(" ",A1)) + VALUE(MID(A1,FIND("hr",A1)-3,3) & ":" & MID(A1,FIND("min",A1)-3,3))

Then select the two columns of data, and sort based on the second column.

For example, select A1:B14, click Data > Sort, select:

Sort by: B
Sort on: Values
Order: smallest to largest or largest to smallests

[sandy666]

but this one is correct

(done with PowerQuery - add-in for Ex2010 is required)

[tim201110]

=sum(iferror(mid(a2,row(indirect("1:"&len(a2))),1)*10^(22-row(indirect("1:"&len(a2)))),)) cse

[FlameRetired]

Another way

Please Login or Register  to view this content.

One other way ... array entered ...

Please Login or Register  to view this content.

	A	B	C	D
1	1 days 15 hrs 49 mins	2389		2389
2	1 days 16 hrs 0 mins	2400		2400
3	1 days 19 hrs 30 mins	2610		2610
4	2 days 15 hrs 15 mins	3795		3795
5	2 days 20 hrs 43 mins	4123		4123
6	4 days 21 hrs 24 mins	7044		7044
7	6 days 7 hrs 21 mins	9081		9081
8	11 days 4 hrs 26 mins	16106		16106
9	18 days 0 hrs 19 mins	25939		25939
10	19 days 3 hrs 5 mins	27545		27545
11	28 days 7 hrs 28 mins	40768		40768
12	29 days 2 hrs 28 mins	41908		41908
13	31 days 8 hrs 34 mins	45154		45154
14	53 days 12 hrs 15 mins	77055		77055