SUM Time in the end of text strings

Excel Tips - Adding 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


Let’s examine it inside out.


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:


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)


The result then becomes:


which is very close to what we need…

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


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.


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


Results in:


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.  :-P  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

Excel Tips - Adding time in the end of text strings 1

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:

Excel Tips - Adding time in the end of text strings 2

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

Excel Tips - Adding time in the end of text strings 3

  • Now you should have the time portion extracted properly:

Excel Tips - Adding time in the end of text strings 4(You may format it back to mm:ss if required)

A simple SUM would do the rest

Excel Tips - Adding time in the end of text strings 5

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

Here’s a Sample File file for you to download.

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


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


      • 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)}



  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.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s