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:

Read the full article:

In a previous video ( 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 ( if you are not familiar with this awesome feature of Excel.

In the next video ( we look at a solution that extends on this formula with the SUMPRODUCT function.

excel,excel campus,text to time in excel,convert text to time,excel text to time conversion,excel text to time format,convert text to time in excel,how to convert time in text format to time format in excel,excel text functions,excel search function,excel mid function,excel max function,excel value function,excel iferror function,excel data cleansing techniques,excel data cleaning,excel data cleansing challenge,