Excel Data Cleansing Challenge: How To Convert Text To Time Values

In this video I share an Excel data cleansing challenge. The challenge is to convert time stored as text into time values.

The rules are simple:

1. You can use any Excel tool that you'd like (formulas, Power Query, VBA, etc.)

2. The results must be recognized as time values. See video for explanation.

Additional Resources:

*The challenge file does contain Tables, but you do not have to use Tables to complete this challenge.

👉 How to Submit Your Solution:

Please leave a comment below 👇 and explain your solution. You can also upload your file to a cloud sharing service (OneDrive, Google Drive, Dropbox) and share the link.

I will create another video in the next few weeks that walks through some of the most popular or interesting solutions that are submitted.

Have fun & good luck! 🙂
00:00 Introduction
00:11 Data Cleansing Challenge
01:06 Data Cleansing Challenge Rules
01:45 Format Cells

[JSUG]

Excellent challenge. I used Power Query and created columns for hours, minutes, and seconds, then combined using #time.

[cathycordes]

First time ever using Flash Fill. Loved it! Super new to the world of excel and I need to get up to speed super quick for large projects coming down the pipeline.

[ivanzhelyazkov]

Done with PQ in 12 steps. Mostly Split/ Merge columns + some custom ones. Excellent challenge indeed :)

[entertainmentgalaxy]

nice challenge and flash fill works for this. simple, quick and easy but not dynamic.

[wayneedmondson]

Hi Jon.. inspired by Jack Sugrue below, I devised a Power Query solution:
- load table to PQ; duplicate Time column (to preserve a clean original column)
- replace values: hour for hours, minute for minutes, second for seconds (to isolate to 3 delimiters to be used below)
- Extract Text Before Delimiter " hour" to produce column with just the number of hours; if no hours are in Time column, then fields are left blank (will be filled with 0's later)
- Extract Text After Delimiter "hour " to produce a column with what remains to the right of "hour " (preparing for the next Extract Text Before Delimiter step
- rinse and repeat above to isolate the minutes and seconds; again there will be blanks if no minutes or seconds in source column; the trick above is to do the Extract by Advanced.. scan from end
- once each column has been produced with the hours, minutes and seconds, rename the columns accordingly and remove un-needed columns
- select H/M/S columns and Replace empty fields with 0
- add a custom column with the following formula: =#time(Value.FromText([Hours]), Value.FromText([Minutes]), Value.FromText([Seconds])); change the format to time
- deleted all un-needed columns and close and load back to EXCEL; change format of loaded column to: h:mm:ss
The loaded column should be identical to your solution and be representative of the goal of the challenge.. changing the text numbers and words to a true formatted numerical result. That's my PQ solution. I'm a noob at PQ, so there may be better ways to approach it, but it gets the job done. Let me know what you think. Thumbs up!

[wayneedmondson]

Great idea Jon.. gets the brain cells working! Using your solution as my inspiration, I designed the following formula, entered in Cell D2 and copied down:
=TIME(IFERROR(LEFT($C2, SEARCH("Hour", $C2)-2), 0), IFERROR(MID($C2, SEARCH("Minute", $C2)-3, 3), 0), IFERROR(MID($C2, SEARCH("Second", $C2)-3, 3), 0))
Looking forward to understanding how to tackle this in Power Query and VBA. Thanks for this excellent and thought provoking challenge! Thumbs up!!

[a.j.wilkes]

I had no idea I would find string parsing so satisfying with my Accounting/Economics background, but thanks to your posts I'm enjoying what VBA has been able to do for my career.


I went the VBA route. First seeing if the cell contains "hour", "minute", or "second", and then if it does creating a truncated string up to where it finds the keyword so the numbers are at the end of the string. Then I wrote a function to give me the where the first non-numeric character is in the StrReverse of the keyword string. Lastly taking a right(strHour, # from function) converted to long value and putting the TimeSerial results into the offset column.

[michaelchang]

I have tried flash fill and it works although the data is not consistently have hour, minutes & seconds

[barryhodgetts]

Interesting challenge. look forward to the answers. IF hours =IFERROR(MID(C2, 1, IF(FIND("hour", C2, 1)=4, 2, 1)), 0) for minutes =IFERROR(MID(C2, IF(FIND("minute", C2, 1)-3=0, 1, FIND("minute", C2, 1)-3), 2), 0) with seconds simular to minuted. But initialle used minutes and not minute.

[shabbirkanchwala-abwaab]

Replace Hours/ Minute/ Seconds with ":"
format column as h:mm:ss
Done

[JonathanExcels]

=TIME(IFERROR(MID(C2, FIND("hour", C2)-3, 2), IFERROR(MID(C2, FIND("hour", C2)-2, 1), 0)), IFERROR(MID(C2, FIND("min", C2)-3, 2), IFERROR(MID(C2, FIND("min", C2)-2, 1), 0)), IFERROR(MID(C2, FIND("sec", C2)-3, 2), 0)) all cells match your proof. I didn't look at your solution until after I was done. basically the same solution. I went another route and was going to multiply just the numeric values. An hour is 1/24th of a day so you could replace the word 'hour' with '*1/24' etc... then multiiply but I couldn't find a way to take my text string of math and make Excel calculate it.

[andreferreira]

I like this challenges! it make us think wich is the most efficient way to do things.
i' was doing with vba, but i went with formulas instead (just for fun).
This is how i did it:

=TIME(
)

Regards,

[donpistulka]

Step 4 should be drag above formula to right and down

[karinaadcock]

Hour: =IFERROR(LEFT(C2, FIND("h", C2)-2), 0)
Minute: =IFERROR(MID(C2, FIND("m", C2)-3, 2), 0)
Second: =IFERROR(MID(C2, FIND("sec", C2)-3, 2), 0)
Then put them together: =TIME(Hour, Minute, Second)

[GodGladiator]

This is my solution.
=TIME(IFERROR(TEXT(VALUE(MID(C2, 1, SEARCH("Hour", C2)-1)), "00"), "00"), IFERROR(TEXT(VALUE(MID(C2, MAX(SEARCH("Minute", C2)-3, 1), 2)), "00"), "00"), IFERROR(TEXT(VALUE(MID(C2, MAX(SEARCH("second", C2)-3, 1), 2)), "00"), "00"))

[Kiba_Byte]

How do I minimize a column like you did in the beginning with D?

[donpistulka]

Steps:

1 Copy/Paste column C into Col D

2 Text to Columns - Column D using space as the delimiter.

3 Place the formula: IFERROR(INDEX($D2:$I2, MATCH(J$1, $D2:$I2, 0)-1), 0) in cell J2,

4 Drag above formula to the left and down.

5 Use Time formula on column J, K, L

[ricos]

=SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("minutes", C2, 1)), "00:", "")&SUBSTITUTE(IF(ISERROR(FIND("hours", C2, 1)), "00:", "")&C2, " hours ", ":"), " minutes ", ":"), " seconds", IF(ISERROR(FIND("seconds", C2, 1)), "00", ""))*1

I think! I could have added a value formula in around a couple of the substitutes rather than assume there are always spaces around the hours and minutes texts. I'm getting lazy....

[ExcelWizard]

1 year late 😁
=SUMPRODUCT(TEXT(MID(0&C2, FIND({"h", "m", "se"}, 0&C2&"hmse")-3, 2), "0;;;\0")/24/60^{0, 1, 2})

[gilbertcadman]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Name", type text}, {"Location", type text}, {"Time", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Time], " ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each if List.ContainsAny([Custom], {"hour", "hours"}) then [Custom] else List.InsertRange([Custom], 0, {0, "hour"})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom2", each if List.ContainsAny([Custom1], {"minute", "minutes"}) then [Custom1] else List.InsertRange([Custom1], 2, {0, "minute"})),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom3", each if List.ContainsAny([Custom2], {"second", "seconds"}) then [Custom2] else List.InsertRange([Custom2], 4, {0, "second"})),
#"Extracted Values" = Custom3", {"Custom3", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom3", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"hh", "Custom3.2", "min", "Custom3.4", "sec", "Custom3.6"}),
#"Merged Columns" = Column by Delimiter", {{"hh", type text}, {"min", type text}, {"sec", type text}}, "en-GB"), {"hh", "min", "sec"}, Combiner.CombineTextByDelimiter(":", QuoteStyle.None), "Time.1"),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Columns", {"Name", "Location", "Time", "Time.1"}),
#"Changed Type2" = Other Columns", {{"Time.1", type time}})
in
#"Changed Type2"