## SUM Time in the end of text strings

Did you encounter a task like this?  I did.  Actually it is not as difficult as you may expect.  It could be done in just a minute IF the data is not too bad, like the one shown above.

I am going to show you two approaches to handle this:

1. Formula;
2. A combination of simple techniques that even a regular Excel user should be able to manage.

## 1) Formula Approach

` =SUMPRODUCT(("0:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))+0)`

Let’s examine it inside out.

` RIGHT(A2:A21,6)`

Since we are so lucky that the time we need are all sitting in the end of a text string, enclosed by (),  we may use RIGHT(A2:A21,6) to extract the time portion from the text string.  And this is the resulting array:

`{"(8:12)";"51:36)";"41:18)";"38:15)";"43:20)";"42:58)";"(3:36)";"43:36)";"38:49)";"47:13)";"41:34)";"50:30)";"45:14)";"20:21)";"22:54)";"21:41)";"20:06)";"58:24)";"21:27)";"45:21)"}`

Because the time is in m:ss format, there are cases of “(” being extracted.  Thus we need to

Remove both “(” and “)” from the array by using SUBSTITUTE(text, old text, new text)

` SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")","")`

The result then becomes:

`{"8:12";"51:36";"41:18";"38:15";"43:20";"42:58";"3:36";"43:36";"38:49";"47:13";"41:34";"50:30";"45:14";"20:21";"22:54";"21:41";"20:06";"58:24";"21:27";"45:21"}`

which is very close to what we need…

Concatenate “0:” (zero colon) to the resulting array

` ("0:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))`

As Excel would interpret the result as hh:mm we need to concatenate “0:” to the above array to make sure that the resulting time will be interpret correctly as mm:ss.

`{"0:8:12";"0:51:36";"0:41:18";"0:38:15";"0:43:20";"0:42:58";"0:3:36";"0:43:36";"0:38:49";"0:47:13";"0:41:34";"0:50:30";"0:45:14";"0:20:21";"0:22:54";"0:21:41";"0:20:06";"0:58:24";"0:21:27";"0:45:21"}`

Adding 0 to the above result to convert the text into a real time value

` ("0:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))+0`

Results in:

`{0.00569444444444444;0.0358333333333333;0.0286805555555556;0.0265625;0.0300925925925926;0.029837962962963;0.0025;0.0302777777777778;0.0269560185185185;0.0327893518518519;0.0288657407407407;0.0350694444444444;0.031412037037037;0.0141319444444444;0.0159027777777778;0.0150578703703704;0.0139583333333333;0.0405555555555556;0.0148958333333333;0.0314930555555556}`

Feeding the array into SUMPRODUCT to give the total

i.e. 0.49056712962963 or 11:46:25 (format as [h]:mm:ss)

Not really difficult… 🙂

If you are good in using FUNCTIONS and know about array formulation, it should not be too difficult.  Nevertheless, I know that there are quite a lot people using Excel are having Formula-phobia.  😛  That’s why I am going to show you an non-formula approach, which may be even faster.

## 2) Non-formula approach.

Copy another set of your data and paste it on the next column

Using Find and Replace to get the time portion (tip: CTRL+H to open the Find and Replace dialog box.  Note: Select the range you want to perform Find and Replace beforehand, otherwise your original data will be in danger…)

• First Find *( and Replace with 00:

• See the changes?!  Then Find ) and Replace with nothing

• Now you should have the time portion extracted properly:

(You may format it back to mm:ss if required)

A simple SUM would do the rest

Did you notice that the above steps are actually the same as the ones described for formula-approach?!

Honestly, if it is an one-off task, I would absolutely do it in the way demonstrated in the 2nd approach.  How about you?

This entry was posted in Excel Tips, Formula and tagged , , , , , , , . Bookmark the permalink.

### 6 Responses to SUM Time in the end of text strings

1. Leonid says:

Flash Fill with {SUM((B8:B27/60))} also works

Like

• MF says:

Hi Leonid,
I don’t know Flash Fill can do that… Actually I haven’t tried Flash Fill as I am still using Excel 2010.
Appreciate if you could elaborate more on how it works.
Cheers,

Like

• Leonid says:

Hi @MF,
In Excel 2013:
1. In cell B8 type 8:12. It tells Excel what pattern we’d like to use transforming data in A8:A27.
2. With B8 selected click Flash Fill in Data Tools section on Data ribbon. It fills B8:B27 with time values.
3. To get total time we need to divide time values by 60 and sum them up as =SUMPRODUCT(B8:B27/60) or array-entered ={SUM(B8:B27/60)}

Cheers,
Leonid

Like

• MF says:

oic. Thanks for your input, Leonid.
I wish I have Excel 2013 (or even Excel 2016). 🙂

Like

2. RickB says:

For the one-time approach, use Text-to-Columns to parse the data into columns. One of the columns will have the time in parentheses. e.g., (8:12)
To do the rest with forumla instead of search and replace, you could then use MID to grab the time text without the parentheses. (e.g., =MID(C8,2,LEN(C8)-2)
Concatenate “0:” on the front as you’ve suggested, convert to a value and add them up.

Like

• MF says:

Hi RickB,
Good suggestion!
thanks😀

Like