OmniDoc# Core comparison
This article organizes commonly used text cleaning functions in Excel, explains what types of problems each function handles, and provides steps and verification methods for using auxiliary columns to clean data, helping you choose the right function and preserve the original data.
#What are the commonly used functions for Excel data cleaning and how to use them?
Key points of this article
- TRIM removes leading and trailing spaces and extra spaces, and CLEAN removes unprintable characters. They are often used in combination, but neither can handle non-breaking spaces.
- Use the auxiliary column to write the formula first, without overwriting the original data directly. After confirming the result, paste it as a numerical value.
- Use LEN to compare the number of characters before and after cleaning. The difference should be consistent with the number you expect to delete.
- Text-type numbers cannot be summed. You can use VALUE or the column function to convert them, and then check the total after conversion.
- To intercept by position, use LEFT, MID, and RIGHT. To intercept by separator, use FIND positioning.
Customer lists exported from the system, tables copied from web pages, and data transferred from PDF all have similar common problems: there are extra spaces at the beginning and end, invisible characters are sandwiched between them, numbers are saved as text, and the date format in the same column is confusing. Manual modification of hundreds of lines is slow and easy to miss. There is a set of functions in Excel that specialize in processing text, each dealing with a different type of problem. By choosing the right function, you can sort out a column of data in just a few steps.
This article is written for office workers, students, and data analysis newbies who need to organize tables. Focus on answering two questions: which type of cleaning work each common function is responsible for, and which one should be used when encountering specific problems; and how to complete cleaning and inspection without damaging the original data.
Give the conclusion first.
Which function should be used to clean cell text? One sentence conclusion: first determine which category the problem belongs to, then select the corresponding function, use the auxiliary column to write the formula, and then replace the original data after confirming that it is correct.
| your situation | Recommended practices | reason |
|---|---|---|
| There are extra spaces before, after or between the text | TRIM | Remove leading and trailing spaces and compress consecutive spaces into one |
| Invisible characters after copying from web page or system | CLEAN with TRIM | CLEAN removes unprintable characters, TRIM handles spaces |
| The spaces look normal but cannot be cleared | SUBSTITUTE replaces CHAR(160) | This is usually a non-breaking space, TRIM does not handle it |
| Numbers are left aligned and summed to 0 | VALUE or column-by-column function | Numbers are stored as text and need to be converted to numeric values |
| A cell contains multiple pieces of information | MID, LEFT, RIGHT combination FIND | Split information by position or separator |
Before you start: Confirm these points first
Judgment before taking action determines how much trouble will be saved later.
- See clearly what the problem is first. Select a few lines for visual inspection, and then use LEN to count the number of characters. More characters than expected usually indicate hidden spaces or invisible characters.
- Keep original data. Leave an empty column to the right of the data to write the formula without directly overwriting the source column. In case the formula is written incorrectly, the original data is still there and can be rolled back.
- Distinguish between text numbers and numerical values. Numbers are right-aligned by default. If they are displayed on the left, they are mostly text. Text numbers cannot participate in the sum and may also cause abnormal sorting results.
- Confirm data source. Content copied from web pages or other systems can easily contain invisible characters. Tables transferred from PDF have common problems of merging cells and wrapping lines. If the data is in PDF, you can first use the PDF editor of the OmniDoc desktop client to export it to Excel format and then clean it; the scanned document needs to be OCR recognized first, and the recognition results must be manually checked.
- Write down cleaning standards. For example, the date is unified into "year-month-day", the mobile phone number removes spaces and dashes, and the province and city in the address use a unified abbreviation. Only when the standards are clearly written can the formula be easily determined.
Steps to clean a column of text using auxiliary columns
The following takes the customer name in column A as an example to demonstrate the complete process.
- Write the formula
=TRIM(A2)in the first row of column B (or any empty column to the right of the data), referencing the original cell. - If you need to remove invisible characters at the same time, change the formula to
=TRIM(CLEAN(A2)), remove the unprintable characters first, and then process the spaces. - Fill the formula down to the last row and check row by row whether the result is as expected.
- Use
=LEN(A2)-LEN(B2)in another column to count the number of deleted characters in each line, and confirm that the difference is consistent with your judgment. - Select column B, copy and paste it back to column A using the value option of "Paste Special", or save it to a new column to fix the formula result.
- Delete the auxiliary columns and save as a new file to avoid confusion with the original file.
If you want to extract the middle district name from a text like "Beijing-Chaoyang-0012", you can use =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1). The idea is to first use FIND to find the positions of the two separators, and then let MID intercept the content between them.
Check each item after completion
Completion of cleaning does not mean that the results are correct. It is recommended to check each item.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether leading and trailing spaces are cleared? | Use LEN for the result column to see if the length is consistent with the naked eye expectation. | Check whether the line number referenced by the formula is misplaced |
| Can figures participate in the calculation? | If you use SUM for this column, the result is zero, which means it is still text. | Use VALUE conversion, or batch conversion with sorting function |
| Whether invisible characters remain | Compare the LEN results to find out the lines that are abnormally large | Replace CHAR (160) with normal spaces with SUBSTITUTE |
| Is the date format uniform? | Look at the alignment and spot check the display effect of several lines | Use DATEVALUE to convert, or uniformly format cells |
| Whether there is an error value | Filter or find # VALUE! Or something like that. | Package with IFERROR, or go back to the source data for correction |
The result of machine recognition or automatic conversion, such as a form recognized by OCR, especially to check numbers and names one by one, the machine may mistake similar characters.
| Function | Main use | Example | Matters needing attention |
|---|---|---|---|
| TRIM | Remove the leading and trailing spaces and compress the excess spaces in the middle | =TRIM(A2) | Do not handle uninterrupted spaces |
| CLEAN | Remove non-printable characters | =CLEAN(A2) | Do not handle normal spaces |
| SUBSTITUTE | Replace by content | =SUBSTITUTE(A2,"-","") | All matches will be replaced |
| REPLACE | Replace by location | =REPLACE(A2,1,3,"") | The wrong position will correct the wrong content. |
| LEFT, MID, RIGHT | Intercept by location | =LEFT(A2,4) | Often used in conjunction with FIND |
| FIND | Positioning character position | =FIND("-",A2) | Returns an error value when it cannot be found |
| VALUE | Convert text to number | =VALUE(A2) | Times error with non-numeric characters |
| PROPER, UPPER, LOWER | Unify English case | =PROPER(A2) | Affect only the English alphabet |
| CONCAT, TEXTJOIN | Merge multi-column or multi-grid content | =TEXTJOIN(" ",TRUE,A2:C2) | TEXTJOIN is not available in some older versions |
| IFERROR | Display alternate content in case of error | =IFERROR(VALUE(A2),"") | May cover up the real problem, use it with caution |
# frequently asked questions (FAQ)
1. After being processed by TRIM, why can't I clear the blank space?
It is most likely an uninterrupted space (character encoding 160). TRIM only recognizes normal spaces, and you need to replace it with =SUBSTITUTE(A2,CHAR(160)," ") before using TRIM for the results.
2. Can the results of the formula be directly used as ordinary data?
The result of the formula depends on the original column, which will change when the original column changes. After confirming that it is correct, you should use the numerical option in selective paste to fix the formula results, and then delete the source or auxiliary columns.
3. There is a large amount of data and the same cleaning has to be repeated every week. Is there a more convenient way?
You can consider the data query and conversion tools that come with Excel, record the cleaning steps and run again the next time you import new data. Tasks with fixed steps are suitable for this approach, and one-time small tables with auxiliary columns are sufficient.
4. The form is in PDF. Can you clean it directly?
PDF usually cannot be treated directly as a table. The text version of PDF can be exported to Excel format before cleaning; the scanned version needs to be identified by OCR first. Either way, you have to check the numbers and text against the original file after export or identification.
# Summary
The key to Excel data cleaning is to determine whether the problem belongs to spaces, invisible characters, text numbers or the content that needs to be split, and then select the corresponding function. Using the auxiliary column to write the formula, using LEN to check the results, and then fixing it to numerical values, this process can make the cleaning process fast and controllable.
