Cleaning Messy Data 3 Methods- Mind Blowing Bonus at the end, by Officeinstructor on YouTube. 5 min 32 s.
- Video length
- 5 min 32 s
- Time to do it
- 15 minutes
- Difficulty
- Medium
- Steps
- 8
What you need
Steps
Open your messy sheet and look at a few sample cells. Note the symbols or separators that repeat, such as dashes, slashes or brackets.
Add a blank helper column next to the messy data. You will put the clean result there and keep the original untouched.
Use TEXTAFTER when the clean text always sits after one known character. Type =TEXTAFTER(A2,"-") and press Enter.
Fill the formula down the column by dragging the small square at the cell corner. Check a few results against the source cells.
Use REGEXEXTRACT when the pattern varies. Type =REGEXEXTRACT(A2,"[A-Za-z]+") to pull only letters, or adjust the pattern to your data.
Fill the REGEXEXTRACT formula down and compare both columns. Keep the method that gives correct results for every row.
Copy the clean column and paste it as values with Ctrl+Shift+V or Paste Special. This removes the formulas so the text stays fixed.
Delete the helper formulas you no longer need and keep the original messy column until you have checked the output.
Tips and common mistakes
Test your formula on the oddest-looking row first, since that is where a pattern usually breaks.
Wrap the formula in IFERROR so rows that do not match show a blank instead of an error.
Paste as values before sorting or sharing the sheet, or the formulas may point at the wrong cells.
Questions
What if TEXTAFTER or REGEXEXTRACT is not in my Excel?
These functions need a current Microsoft 365 subscription. Older perpetual versions like Excel 2019 do not have them, so you would need LEFT, RIGHT, MID or FIND instead.
Can I clean several columns at once?
Yes. Write the formula for the first column, fill it down, then copy the formula across to the other columns and adjust the cell references.
Will this change my original data?
No. The formulas only read the original cells and write results in the helper column, so your source data stays as it is.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
