Bottom line: Learn how to use VBA and User Defined Functions (UDFs) to convert text to time values.
Skill level: Intermediate
The Data Cleansing Challenge
This post is the third in a series on solutions to the data cleansing challenge I presented in a previous post. The challenge is to use any tools in Excel to convert the time/duration stored a text into a numeric value that can be formatted as a time.
The original data set contains a column of time/duration text that are in the following format.
1 hour 27 minutes 15 seconds
We need to convert it to a number in Excel with a time format of h:mm:ss that looks like the following.
UDF Solutions with VBA
In this post & video we look at a solutions using User Defined Functions (UDFs) with VBA. The UDFs allow us to pass the text through as an argument the function in a cell, then process/calculate the result with various coding techniques in VBA, and output the result to the cell.
There were quite a few different UDF solutions. Thanks to Jon Peltier (peltiertech.com), Graham (a member of our VBA Pro Course), Charles Marshall, and everyone else for participating!
Download the Excel File
Download the example Excel file to follow along.
Text To Time Challenge - VBA Solutions.xlsm (367.4 KB)
If you're new to UDFs then checkout my last post on How to Write User Defined Functions in VBA.
In the video below I walk through the different functions (macros) that were submitted. Each function uses a variety of different coding techniques including loops, arrays, if statements, select case statements, and more.
I encourage you to download the file and step through the code. This will help you learn a lot more about the techniques that are used here.
VBA is another great way to solve this challenge. There is so much versatility with writing our own functions, and the possibilities are endless.
In my post on how to write User Defined Functions, I share some pros & cons of using UDFs in your projects.
The most important thing to know is that the function code will likely need to be stored in the Excel file that you are using the function in. This is especially true if you are sending the file to other users.
Please leave a comment below with any questions or suggestions. Thank you! 🙂