Convert Text to Time Values with UDFs in VBA

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.

1:27:15

UDF Solutions with VBA

There were a ton of awesome solutions submitted on the original post and YouTube video. Thanks again if you submitted a solution!

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.

If you're new to UDFs then checkout my last post on How to Write User Defined Functions in VBA.

Video Tutorial

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.

Conclusion

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.

If VBA isn't your thing, then checkout the previous posts on solving this challenge with formulas and Power Query.

Please leave a comment below with any questions or suggestions. Thank you! 🙂