Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint-registration/

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:

Read the full article: https://www.excelcampus.com/functions/convert-text-to-time-values-formulas/

In a previous video (https://youtu.be/uhzLYTupl9I) I shared this challenge to convert time/duration stored as text into time values that can be used for calculations and analytics.

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.

In the video I use an Excel Table. Checkout my video on a Beginner's Guide to Excel Tables (https://youtu.be/fJnJTff4Na8) if you are not familiar with this awesome feature of Excel.

In the next video (https://youtu.be/TzEVSlguFso) we look at a solution that extends on this formula with the SUMPRODUCT function.
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