Yes, I know you should use Power Query to clean data and I demonstrated how to do that in my previous post. Sometimes it is easier to record a macro because a macro can clean the data in place.

The problem with Power Query is that it has to create a separate data set to clean the data. You have an existing Excel table with issues and Power Query can fix those issues. But it has to output to a new, separate output table to achieve that.

In simple cases a macro can fix the data in place. In the video below I take you through recording a macro that uses find and replace to fix the Timestamp column from last week’s post.

The problem with the data is that it is supposed to be time but it has letters in the entries that means Excel treats it like text, not time. The previous post, showing more solutions, is here.

If you are new to macros this blog posts covers some of the basics.

Some of the keyboard shortcuts used in the video are.

Ctrl + Shift + Down arrow – selects a continuous vertical range, down from the starting cell.

Ctrl + H – opens the Find and Replace dialog.

Ctrl + 1 – opens the Format Cells dialog. Use the 1 on the keyboard (not the 1 on the numeric keypad) and this shortcut opens format on whatever you have selected.

Alt + F11 – opens the VBA windows (Visual Basic for Applications) where the macro code is stored.

F5 – in the VBA window runs the current macro.

The video is about 5 and a half minutes long.

Macro Warning: Macros cannot be undone. Macros also clear the Undo list so you can’t undo anything you did before you ran the macro. So always save your file before running a macro so you can close and not save to return to the pre-macro state.