Cleaning messy data in Excel is essential for accurate analysis and efficient workflow. Whether you’re dealing with extra spaces, incorrect formatting, or inconsistent data, these five powerful Excel data-cleaning tools will help you tidy up your spreadsheets effortlessly.
Excel Practice Files
1. Trim Function – Removing Unwanted Spaces
Problem: Extra spaces in your data can cause errors when sorting, filtering, or comparing values. For example, a lookup function may fail to match values correctly if you have a dataset with extra spaces before or after names. Similarly, sorting a list with leading spaces can result in unexpected orderings, making it difficult to analyse data accurately.
Solution: The TRIM function is a data cleaning tool that removes all extra spaces except for single spaces between words.
How to Use:
- Use the TRIM function to remove unnecessary spaces.
- Works well when dealing with data imported from external sources.
Step 1: Select the first cell where you want to return the data without spaces (e.g., cell B4).
Step 2: Enter the formula =TRIM(A4) and press Enter.
Step 3: Drag the fill handle down to apply the formula to the entire column if needed.
Step 4: If needed, copy the cleaned data and replace the original data with Paste Special > Values.
Note: If the TRIM function does not remove spaces, you may need to refer to the steps outlined in my blog post remove spaces Excel TRIM function misses.
2. Text to Columns – Splitting Data & Fixing Formats
The Text to Columns tool is an incredibly powerful data-cleaning tool in Excel that helps clean data in multiple ways. Whether you need to split the text into different columns, fix incorrect date formats, or convert numbers stored as text into proper numeric values, this tool makes data cleaning easy and efficient.
Splitting Data into Separate Columns
Problem: Data stored in a single column (e.g., names, dates, and times) must be split into multiple columns.
Solution: The Text to Columns tool separates text based on a delimiter (a character or symbol that indicates where data will be split into different columns), such as commas, spaces, or tabs.
How to Use:
Step 1: Select the column containing the full names you want to separate.
Step 2: On the Data tab in the ribbon, click Text to Columns.
Step 3: Choose Delimited and click Next.
Step 4: Select the delimiter that separates the first and last names (commonly a space) and click Next.
Step 5: Choose where to place the separated columns or let Excel overwrite the existing column.
Step 6: Click Finish to complete the process.
Resetting Dates to the Correct Format
Problem: Some dates may appear incorrectly formatted, e.g., mm/dd/yyyy or dd/mm/yyyy, preventing proper sorting and calculations.
Solution: Use Text to Columns to reset and correct date formatting.
How to Use:
Step 1: Select the column containing the dates with the incorrect format.
Step 2: On the Data tab in the ribbon, click Text to Columns.
Step 3: Choose Delimited and click Next.
Step 4: Leave the delimiter unselected and click Next.
Step 5: From the Date option, select the current date format; e.g. if the date is currently formatted mm/dd/yyyy, select MDY.
Step 6: Click Finish to complete the process. The dates will be reset using the correct date format.
Resetting Text to Numeric Values
Problem: Numbers stored as text won’t calculate correctly in formulas. For example, lookup functions like XLOOKUP and MATCH may not work correctly if numbers are stored as text, as Excel treats them differently from actual numeric values.
Solution: Use Text to Columns data-cleaning tool to quickly convert text-based numbers into numeric values.
How to Use:
Step 1: Select the column containing the numbers stored as text.
Step 2: On the Data tab in the ribbon, click Text to Columns.
Step 3: Choose Delimited and click Next.
Step 4: Leave the delimiter unselected and click Next.
Step 5: Leave the General option selected.
Step 6: Click Finish to complete the process. The dates will be reset using the correct date format.
3. Value Function – Converting Text to Numbers
Problem: Numbers stored as text won’t calculate correctly in formulas.
Tip: Select a cell and look at the formula bar to check if your data is stored as text or a number. If the value is left-aligned by default and has an apostrophe before it (e.g., ‘123), it is stored as text. You can also use the ISTEXT function to confirm, e.g. if =ISTEXT(A1) returned TRUE, cell A1 contains data that is stored as text.
Solution: The VALUE function converts text-based numbers into real numbers.
How to Use:
- Apply =VALUE(B2) to change text-formatted numbers into numeric values.
Step 1: Select an empty cell, usually in the adjacent empty column next to the data stored as text.
Step 2: Enter the formula =VALUE(B2), where B2 is the cell containing the data stored as text, and press Enter.
Step 3: Drag the fill handle down to apply the formula to the entire column if needed.
Step 4: Copy the converted numbers and replace the original data with Paste Special > Values.
4. TEXTBEFORE & TEXTAFTER – Extracting Specific Text
Problem: You need to extract specific text parts. For example, if “[email protected]” is in cell A2, you can use the TEXTBEFORE and TEXTAFTER functions to extract the first, last, and domain names.
Solution: The TEXTBEFORE and TEXTAFTER functions are useful Excel data-cleaning tools for extracting specific text parts from a cell
- TEXTBEFORE extracts everything before a specified delimiter.
- TEXTAFTER extracts everything after a specified delimiter.
How to Use:
- =TEXTBEFORE(A2, “.”) extracts the first name from “[email protected]”.
- =TEXTAFTER(TEXTBEFORE(A2,”@”,1),”.”,1) extracts the surname.
- =TEXTAFTER(A2, “@”) extracts the domain from an email.
- =TEXTBEFORE(TEXTAFTER(A2,”@”),”.”) extracts the company name.
Step 1: Select the first cell where you want to extract text (e.g., B2).
Step 2: Enter the formula required.
Step 3: Press Enter to apply the function.
Step 4: Drag the fill handle down to apply the formula to the entire column if needed.
Step 5: Copy the extracted text and use Paste Special > Values to replace the original data if necessary.
5. CONCAT Function – Joining Text Together
Problem: You want to merge text. For example, if you have a dataset with first names in column A and last names in column B.
Solution: You can use CONCAT to combine them into a full name in column C. This is a particularly useful data-cleaning tool when working with exported data that separates key information into different columns but needs to be displayed together. The CONCAT function joins text seamlessly, replacing the older CONCATENATE function.
How to Use:
- Use =CONCAT(A2, ” “, B2) to merge first and last names.
Step 1: Select the first cell where you want to combine text (e.g., C2).
Step 2: Enter the formula =CONCAT(A2, ” “, B2) and press Enter.
Step 3: Drag the fill handle down to apply the formula to the entire column if needed.
Step 4: Copy the concatenated text and use Paste Special > Values to replace the original data.
Troubleshooting Tips
Even with these powerful data-cleaning tools, you may encounter common issues while cleaning your data. Here are some troubleshooting tips to help resolve them:
1. Text to Columns Not Splitting Data Correctly
- Ensure the delimiter (comma, space, tab) matches the data format.
- If data is inconsistent, try using Find & Replace (Ctrl + H) to standardize delimiters before splitting.
2. TRIM Function Not Removing All Spaces
- If TRIM doesn’t remove spaces, check for non-breaking spaces by using =CLEAN(A1) in combination with TRIM.
- Copy and paste data into Notepad first to strip any hidden characters, then re-paste into Excel.
3. Numbers Still Treated as Text After Using VALUE or Text to Columns
- Check if the column has an apostrophe (‘123) before numbers. Manually remove or use Find & Replace (Ctrl + H) to delete it.
- Apply General or Number formatting to the column before re-entering the function.
4. TEXTBEFORE or TEXTAFTER Functions Returning Errors
- Ensure the delimiter used in the formula exists in every cell; otherwise, Excel will return an error.
- Use IFERROR(TEXTBEFORE(A2, “.”), “No match”) to prevent errors when the delimiter is missing.
5. CONCAT Function Not Merging Properly
- If values aren’t appearing correctly, check for extra spaces using TRIM before merging.
Conclusion
Cleaning data in Excel doesn’t have to be tedious. With these five data-cleaning tools, you can quickly remove unwanted spaces, convert text to numbers, split or merge data, and extract key information efficiently. Try these out, and let me know how they work for you!
Watch The Excel Video Tutorial
Excel Practice File Download
Certified Microsoft Office Specialist
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!
Happy Excel-ling!!