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.

Convert Text Values to Time Values in Excel

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!

Text to Time with Custom Functions UDFs in VBA Excel

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.

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.

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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! 🙂

4 comments

Your email address will not be published. Required fields are marked *

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly