A cluttered Excel sheet can be a nightmare—extra spaces, inconsistent formatting, random text everywhere. But with the right functions, you can clean it up in seconds and make your data actually usable.
Stray spaces on your spreadsheet can make two identical-looking entries different, mess with data validation, and even throw off your formulas. Thankfully,the TRIM functionsolves all that by removing all extra spaces from your texts (except single spaces between words). This function is great for tidying up names, addresses, etc., especially in the sheets you import from other sources.
Let’s say cell A1 containsAlice, with extra spaces everywhere. Using the TRIM function will returnAlicewith no spaces.
Instead of referencing one cell and extending the formula, you can also reference all your data in one go. For instance, I can use the following formula to clean all my stray spaces.
TRIM is often your first line of defense in data cleanup. Many other text functions work better when you’ve already trimmed your data, so don’t skip this step.
I instantly feel like an amateur whenever I haveDIV/0, N/A, VALUE!, and all other fancy Excel errorslittered across my spreadsheets. Maybe I don’t feel that way all the time, but just the sight of them makes me (and my colleagues) so anxious over something that could very well be a little issue.
IFERROR displays something more presentable and less anxiety-inducing. It works with Office 2019 and Microsoft 365 subscriptions, so it’s widely accessible. This function evaluates a formula and returns a specified value if the formula results in an error.
For instance, instead of using a formula that returns #NAME? when no match is found, such as the one below:
You can use the following formula to return a custom message instead:
Your spreadsheet instantly looks more professional, and your boss stops asking why there are error messages everywhere. Plus, you can return whatever you like—blank cells, a dash, custom messages, or even alternative calculations—if and when errors occur.
When youimport data from PDFs, websites, and legacy systemsor evenconvert PDF files into Excel spreadsheets, you tend to carry along some invisible characters. Things like line breaks and hidden symbols, which you can’t see, can tag along and break your formulas.
The CLEAN function removes all non-printable characters from your text, leaving behind a more predictable and formula-friendly version.
you’re able to also pair CLEAN with the TRIM function. Many Excel pros use this as their go-to combo for imported data:
CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), which works for Windows-based files. If your data comes from macOS or web sources, you might need additional tools to clean out Unicode nuisances.
2TEXTSPLIT
Sometimes, all your data ends up crammed into a single cell, separated by commas, semicolons, periods, or line breaks. Having to then extract your data into appropriate rows and columns manually is a pain.
TEXTSPLIT, available in Excel 365 and Excel 2021, works the same as the Text-to-Columns wizard, but in formula form, and it’s great if you’re dealing with concatenated data. This function can split text across columns or down rows using delimiters you specify.
If cell A1 containsAda,Uche,ada.uche@email.com, then using the formula below can split the text into three separate columns:Ada,Uche, andada.uche@email.com.
you’re able to even split by row or column, and handle multiple delimiters. For instance, if cell A2 containsApple,Banana;Cherry, then use the formula below:
This will split the text intoApple,Banana, andCherry.
By default, TEXTSPLIT will fill these values across columns. If you wanted them to split into separate rows instead, set the fourth argument, which is FALSE or omitted by default, to TRUE:
This would result inApplein cell I1,Bananain J1,Cherryin I2, andDatesin J2.
Notice that you’d be using one delimiter (like a semicolon) to split words into columns, and another (like a period) to indicate where a new row should begin.
As a result, this function can get quite complex. It often requires careful planning of your delimiters and potentially combining TEXTSPLIT with other functions to achieve your desired layout. But for extraction, TEXTSPLIT works great.
After splitting, you may want to stitch the pieces back. TEXTJOIN helps you merge multiple text strings with a delimiter of your choice and even lets you skip blanks.
Let’s say you have first names in column A, middle names in column B (some cells are empty), and last names in column C. You can use the following formula to create full names without extra spaces from empty middle name cells.
You’ll get full names likeCardi BorMary Jane Watsonafter applying the formula.
The TRUE argument tells Excel to ignore empty cells, so you don’t get awkward gaps or dangling commas.
Messy spreadsheets are chaotic until you sort them out. But with the right Excel functions, you don’t need hours of manual fixing. So, the next time you open a spreadsheet that makes your eyes twitch, you’ll know exactly what to do.
