Convert Text to Time Values with Text Functions (Data Cleansing Part 1)

This is the first video in a series of solutions for our Data Cleansing Challenge. In this video I explain how to use text functions to convert the time stored as text into numeric time values in Excel.
Download the Excel file to follow along:

Thanks to everyone that commented on the video and blog post with solutions. In this video we look at a solution with text functions to extract the time periods (hours, minutes, seconds).

In the video I walk through writing the formula and you will learn the following functions:

◼ SEARCH - find text within a string of text and return the number of the starting character.

◼ MID - return/extract a portion of the text based on the starting charcter number and length of characters.

◼ MAX - return a 1 if SEARCH returns a zero for the starting character. Used in place of an IF function.

◼ VALUE - convert the text returned by MID to a numeric value.

◼ IFERROR - handles the error returned by SEARCH if the find_text is not found. Return a zero to denote that the time period (hr, min, sec) does not exist in the text.

00:00 Introduction
00:46 Formulas and Text Functions
01:50 Mid Function
03:27 Search Function
07:19 Handling Errors
08:34 Extracting Minutes
14:44 Outro

[reshmark]

Incredibly useful. Can't thank you enough for this video. Keep up the great work. 👍

[vaishika]

You just made my life easier Buddy. Thank you!

[mohamedel-sharkawy]

Really thank you for this great service ❤️❤️❤️

[wayneedmondson]

Thanks Jon.. great challenge and explanation using TEXT functions. Love the use of MAX with SEARCH to trap for the one exception. Also, this makes it pretty easy to put it all in a single TIME function by copying and pasting the formulas for each column into position within the TIME function for h, m, s, eliminating the need for the helper columns. Learned a lot. Looking forward to the next one. Thumbs up!

[dreamchaserJ]

Learn so much with this video. Thanks!

[stevennye]

This solved a problem I had thought about for a long time.

[NannoWappi]

Great value video - Thank you very much

[entertainmentgalaxy]

thanks for sharing and explain solution

[MohAboAbdo]

Thank you very much for this great video and excellent code. Thank you and a thousand thanks.

[Hellohellonada]

What if we also need to include day duration of this as well?

[mahmoudjamal]

I'm trying to use this formula on this time format "5m 4s" but it's not working

[balasubramanianv]

Excellent video, but when i create a pivot table and sum the duration it doesn't sum up the duration.

[vigneshwarank]

Awesome Jon, waiting for sumproduct and VBA method

[DJJSerpico]

Did I miss where column C is being populated from??? I know it's a starting point, but is that an automated column or free text entered?

[wshong]

Can you please suggest how to overcome if the number of hours exceed 24 hours, the formula returns to hour 1.

[infinitypropertysolutions]

The =TIME(A2, B2, C2) is giving me different numbers on google sheet.

[mauricsdj]

There's new formulas I didn't know, but one step I think is missing, which is, hiding the formula in the time column so you can see just the value in the formula bar, hope you can give us a way to do this step...

[tomypower]

yes mention how helpful Windows Protection is?