Extract numbers after a specific word in a cell

Question

I have an XLS file that lists hundreds of various times that are all a single string in a single cell, all in the following format:

Days: 103, Hrs: 12, Mins: 15
Days: 34, Hrs: 8, Mins: 27
Days: 10, Hrs: 16, Mins: 4

I want to order these by highest to lowest, and if I just sort them, the records with hours in the hundreds are listed before the values in the tens. For example, the above list ordered highest to lowest shows as:

Days: 34, Hrs: 8, Mins: 27
Days: 103, Hrs: 12, Mins: 15
Days: 10, Hrs: 16, Mins: 4

What I want to try and do is extract each number value and put it into it's own cell, so I have a separate Days, Hours and Minutes column to make sorting easier, but I can't seem to figure out how to do this. Can someone point me in the right direction?

Answer 1

You can use a combination of string searching functions (MID, FIND and RIGHT) to extract the different components into their own separate cells.

Here's the end result:

Now the formulas for row 2 (you can then drag to fill the other rows):

Days - cell B2:

=MID(A2, LEN("Days: "), FIND(", Hrs:",A2)-LEN("Days: "))

Hours - cell C2:

=MID(A2,FIND(", Hrs: ",A2)+LEN(", Hrs: "), FIND(", Mins: ",A2)-FIND(", Hrs: ",A2)-LEN(", Hrs: "))

Minutes - cell D2:

=RIGHT(A2, LEN(A2)-FIND(", Mins: ",A2)-LEN(", Mins: ")+1)

Answer 2

You have to follow these steps:

1. Select the column that contains the data.

2. Using Find / Replace remove the text, for example :

Find: Days: Replace: ""

Find: Hrs: Replace: ""

Find: Mins: Replace: ""

Now you data looks like:

    103, 12, 15
    34, 8, 27
    10, 16, 4

3. Select the column.

4. Go to Data -> text in Columns, it will open a window like these:

3. Select Delimited and go to the next step, that look like this:

Select Comma and follow the steps.

4. Now your data will look like this:

   | 103| 12| 15 |
   | 34 | 8 | 27 |
   | 10 | 16| 4  |
   

Each value is in its own column.