OmniDocText Functions for Cleaning Data in Excel: A Practical Guide
Learn which text functions clean messy spreadsheet cells beyond TRIM and CLEAN, how to apply them to a whole column, and how to check the results.
Messy spreadsheet data shows up in predictable ways. Names carry extra spaces, numbers arrive as text and sort in the wrong order, product codes hide non-breaking spaces copied from a web page, and capitalization varies from row to row. Many guides stop at TRIM and CLEAN, but those two handle only some of these problems. When a column still looks wrong after them, you need functions that target the specific defect.
This guide gives you a general method for data cleaning functions in Excel-style spreadsheets: identify the defect, choose a text function that removes or rewrites it, test the formula on a sample, and apply it to the whole column. The examples use UniCell, the spreadsheet in the OmniDoc desktop client. The formulas are standard Excel text functions, so they should behave the same way in other spreadsheets that support them.
You will leave with a short list of functions that fix common defects, a checklist for confirming the output, and a way to avoid overwriting data you cannot recover.
Which text function fixes messy cells? The short answer
Match the function to the defect: use SUBSTITUTE for hidden characters, VALUE for numbers stored as text, PROPER or LOWER for inconsistent capitalization, and LEFT, RIGHT or MID to pull out a part of a code.
| Your situation | What to do | Why |
|---|---|---|
| Numbers stored as text, left-aligned and not summing | Wrap the cell in VALUE, after removing stray spaces | Converts the text into a real number so sums and sorts work |
| Non-breaking spaces or line breaks inside text | SUBSTITUTE the character (CHAR(160) or CHAR(10)) with a regular space or nothing | TRIM does not remove these characters |
| Names in mixed capitalization | PROPER for names, LOWER or UPPER for codes | Standardizes case without retyping each row |
| Codes with a fixed prefix or suffix | LEFT, RIGHT or MID to extract the part you need | Separates the usable part from the noise around it |
| Values that should have a known length | LEN to count characters, FIND to locate a separator | Flags entries that are longer or shorter than expected |
Before you start
- Make a copy of the file. Formulas in a helper column are easy to remove, but overwriting original values cannot be undone once you save.
- Choose a sample of 10 to 20 rows that includes the problem cases. You can check a sample by eye, which is much faster than checking thousands of rows.
- Define the target format first. Decide what a clean value looks like, such as a date written day-month-year or a code that is always eight characters. You cannot clean toward a target you have not written down.
- Decide whether you need the original column. Other formulas, lookups or reports may reference it, and replacing it can break them.
- If the table came from a PDF, export it to Excel first. The OmniDoc PDF editor can export to Excel, so you can clean the table in UniCell. Check the exported table against the PDF, because the export may split or merge cells differently from the original.
Applying data cleaning functions in UniCell, step by step
- Open the file in UniCell. Work on the copy you made, so the original stays untouched.
- Find the defect. In an empty column beside the data, type =LEN(A2) and copy it down a few rows. If a value that looks like "Smith" returns 7 when you expect 5, there are hidden characters or extra spaces.
- Write the fix in a helper column. For non-breaking spaces, a formula such as =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) replaces the hidden space with a normal one, then trims the result. Put the formula in the first row of the helper column, not in the original column.
- Test the formula on your sample rows. Compare each cleaned value with what you expect. If a row looks wrong, read the original cell carefully before changing the formula.
- Copy the formula down the entire column. Each row refers to its own row in the original column, so the same formula works across the data. Confirm the last row has a result.
- Handle numbers separately. For numbers stored as text with comma thousand separators, a formula such as =VALUE(SUBSTITUTE(A2,",","")) removes the separators and converts the rest. Whether VALUE reads "1.5" as one and a half depends on your regional settings, so test a value you know.
- Check the output using the table in the next section before you go further.
- Replace the original with the cleaned values. Copy the helper column, then paste it as values into the original column. Pasting values, rather than formulas, keeps the result even if you later delete the helper column.
- Delete the helper column and save under a new name. Keeping the original copy alongside the cleaned file gives you a way to compare or revert.
Check the result
Run these checks on your sample, then on the full column, before you trust the cleaned data.
| Check | How to see it | If it's wrong |
|---|---|---|
| Character counts | Compare LEN of the original and cleaned values | If too many characters disappeared, narrow the SUBSTITUTE target so it matches only the unwanted character |
| Numbers | Sum the column or sort it; check a total you calculate by hand on the sample | Numbers that still behave as text usually need the separator removed or the cell reformatted as a number |
| Errors | Look for #VALUE! or #NAME? in helper cells | #VALUE! usually means the text cannot convert to a number; #NAME? means a misspelled function name or a function the spreadsheet does not support |
| Hidden characters | Compare LEN with the number of characters you can see | Add another SUBSTITUTE for CHAR(10) or other invisible characters |
| Row alignment | Spot-check five rows at the top, middle and bottom | If results shift between rows, the reference may have been locked or pasted incorrectly; copy the formula down again |
If you want to see what a cell actually contains, select it and read the entry line. Invisible characters are not shown in the cell, but they do appear in the length count.
❓ FAQ
1. Can I clean a column without writing formulas?
Yes, for a single known problem. Find and replace can remove one character, such as a non-breaking space pasted from a web page, across the whole column. It cannot handle patterns that vary from row to row, so formulas are a better fit when several defects appear together.
2. Why does a number still look like text after VALUE?
The cell may still be formatted as text, or the decimal and thousand separators may not match your regional settings. Reformat the cell as a number, then test with a value you already know, such as 1,500 or 2.75. Remove any leftover spaces with TRIM or SUBSTITUTE before converting.
3. Should I clean the original data or a copy?
Clean a copy. Keep the original file unchanged so you can compare results, recover values you removed by mistake, and show your work if someone asks how the data was prepared.
4. Do these functions fix spelling errors?
No. Text functions change characters by rule, such as removing spaces or changing capitalization. They do not know whether a word is spelled correctly, so check spelling separately if it matters for your data.
📝 Summary
Most messy cells can be fixed by matching the defect to a specific text function, testing the formula on a sample, and then applying it to the whole column. Work in a helper column, check lengths, totals and errors, and paste values back only after the results look right. With those steps, data cleaning functions in Excel become a repeatable routine rather than a one-off repair.
