OmniDocWhat should I do if there are spaces after using the TRIM function in Excel?
TRIM only removes ordinary spaces and compresses consecutive spaces in the middle. It fails when it encounters non-breaking spaces, full-width spaces and line breaks. This article teaches you how to use LEN and CODE to find redundant characters, and then use the combination of SUBSTITUTE and CLEAN to clear them, and avoid leaving textual numbers in the table.
Key points of this article
- TRIM only removes ordinary spaces (encoding 32) and compresses the consecutive spaces in the middle into one. It does not process non-breaking spaces, full-width spaces, newlines and tabs.
- Compare LEN(A1) and LEN(TRIM(A1)), and then use CODE or UNICODE to see which character it is, and you can determine the cause.
- To clear non-breaking spaces, use SUBSTITUTE to replace them with normal spaces. Use CLEAN for newlines and tabs. Both of them can be nested outside TRIM.
- Before cleaning, copy the result as a numerical value and then overwrite the original data to avoid errors caused by the formula relying on the original column.
When doing data cleaning in Excel, many people subconsciously use the TRIM function. After the formula is pulled down, most of the extra spaces do disappear, but some cells still look misaligned, and VLOOKUP cannot find the results. This is often not because the formula is written incorrectly, but because TRIM cannot control those characters.
This article is for people who have used TRIM but still have gaps in their data. After reading, you will be able to determine what kind of character is left, know what formula or operation to use to clear it, and understand how to avoid this type of problem from recurring.
The following method is based on universal Excel functions and does not depend on a specific software. If the data comes from web pages, PDFs or OCR recognition results, it is still recommended to spot check key data after cleaning, especially amounts, numbers and identity information.
There are spaces after TRIM, how to clear them? One sentence conclusion
TRIM only handles ordinary spaces encoded as 32, so the remaining spaces are mostly non-breaking spaces, full-width spaces, newlines or tabs, which need to be replaced separately with the corresponding function. The table below provides methods based on your situation.
| your situation | Recommended practices | reason |
|---|---|---|
| Copied from a web page or Word, LEN is a fixed number more than LEN(TRIM) | Use SUBSTITUTE to replace CHAR(160) with normal spaces, and then apply TRIM | Web pages and typesetting software often use non-breaking spaces to prevent line breaks, but TRIM does not recognize them. |
| The spaces created by the Chinese input method are visually very wide | Replace UNICHAR(12288) (full-width space) with SUBSTITUTE | The encoding of full-width spaces is not 32, and TRIM will not delete it. |
| There are forced line breaks or invisible line breaks in the cells. | Use CLEAN to delete control characters coded 0 through 31 | Newlines and tabs are control characters and require CLEAN processing |
| There is a green triangle in the upper left corner of the number, and the sum result is 0 | After clearing the blanks, use column separation or VALUE to convert to numerical values. | Text numbers are still text even if spaces are removed. |
| The formula result looks the same as the original data, but the search fails. | Check the character encoding first, and then clean the data on both sides uniformly. | Search for the distinguishing characters themselves. Differences invisible to the naked eye will cause matching to fail. |
First determine what kind of problem it is
Before replacing, use the following table to confirm the type of remaining characters to avoid problems after blind replacement.
| symptoms | Common causes | How to confirm |
|---|---|---|
| LEN (A1) is more than LEN (TRIM (A1)), and the number of extra spaces is not consistent with that of naked eyes. | There are uninterrupted spaces or full-width spaces | Check the character encodings of suspicious locations one by one with CODE or UNICODE |
| The contents of the cell are still not aligned after wrapping | There are line breaks (code 10) or tabs (code 9) | Check the edit bar to see if there are any branches, or use CODE to check |
| The two columns look exactly the same, and VLOOKUP returns # NambiA | There are invisible characters at the beginning, end or middle, or the number is in text format | Use LEN on both sides, and use ISTEXT to determine whether it is text or not |
| The SUM result is 0, but the number looks normal. | Numbers are stored as text | Check with ISTEXT. If TRUE is returned, it will be text. |
| The result of the formula is still different from that expected after cleaning. | Clean the original column on which the formula depends, not the result column | Check whether the cell referenced by the formula has been changed |
# item-by-item troubleshooting and repair
When checking, it is recommended to look at the length first, then look at the code, and finally replace it, so that it is easy to locate the problem.
- Enter = LEN (A1) and = LEN (TRIM (A1)) in the blank column, and the difference between the two results indicates that there are extra characters.
two. Type = LEN (A1)-LEN (TRIM (A1)) next to it, and the difference is the number of characters to clean up.
- Check the encoding of the third character (the number can be changed according to the actual location) with = UNICODE (MID (A1Jing 3jin1)) for the suspicious location. The code is 160 uninterrupted spaces, 12288 full-width spaces, and 32 normal spaces.
- After confirming the type, replace it with the corresponding method below; if there are several characters at the same time, you can use it in combination.
- After confirming that the results are correct, copy the cleaning column, use "selective paste" as the value, and then overwrite the original data to avoid confusion caused by subsequent formulas quoting each other.
uninterrupted spaces
Uninterrupted spaces are common in web pages and text copied by typesetting software, and TRIM cannot delete them. You can replace it with a normal space with SUBSTITUTE and compress it with TRIM:
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))
In Windows, you can also hold down the Alt key and enter 0160 in the keypad in the find and replace box, and then leave the replacement blank or fill in the normal blanks.
full-width spaces
The full-width space typed by the Chinese input method is coded as 12288. You can specify it with the UNICHAR function and replace it:
=TRIM(SUBSTITUTE(A1,UNICHAR(12288)," "))
UNICHAR is available in newer versions. If the version is older, you can enter a full-width space directly in the quotation marks of the formula as the search.
Line feeds and tabs
Line breaks and tabs are control characters, and the CLEAN function removes all non-printable characters encoded between 0 and 31:
=TRIM(CLEAN(A1))
Many characters are often mixed in the actual data, so you can combine the three steps together and process them all at once:
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),UNICHAR(12288)," ")))
text-based numbers
After clearing the white space, if the number still cannot be summed up, it is still text. You can first clean it with the above formula, then select the column, use Excel's sorting function to complete the conversion directly in the wizard, or you can wrap it in the formula with VALUE, such as = VALUE (TRIM (CLEAN (A1), but make sure that the cleaned content is indeed a number.
# how to avoid the next occurrence
- check the source first : when copying data from web pages, PDF tables or OCR results, most of the residual characters come from these sources. Make a LEN comparison in the blank column before pasting. - uniform cleaning rules : it is recommended to always use a set of formulas for the same batch of data, such as the combination of TRIM, CLEAN and SUBSTITUTE, to avoid temporary patching. - check after cleaning : add a column of LEN next to it to confirm that the length difference is 0, and then search or summarize it. - numbers and numbers are in a separate fixed format : contents such as identity number and product code are set to text before import, and the amount category is unified as a numerical value to reduce later conversion. - if the data comes from a PDF table *: you can first export the table to an Excel file using the PDF editor of the OmniDoc desktop client, and then follow the steps above to clean it. After export, the figures still need to be checked against the original text.
# frequently asked questions (FAQ)
1. Can the CLEAN function remove all spaces?
No, I can't. CLEAN only removes unprintable characters with codes from 0 to 31, such as line breaks and tabs. Normal spaces (32) and uninterrupted spaces (160) are not processed and need to be used with SUBSTITUTE and TRIM.
2. Why do two cells look exactly the same, but VLOOKUP still can't find them?
It is likely that one of them contains invisible characters, or text numbers on one side and numeric values on the other. Check both sides with LEN and ISTEXT respectively, and then clean them uniformly with the above method.
3. Do you want to keep the original data after cleaning?
It is recommended to keep it. First complete the cleaning of the new column, confirm that it is correct, and then overwrite the original column with a numerical value, so that you can fall back if something goes wrong.
4. What is the reason why the formula turns out to be zero or wrong value after replacement?
It is usually the cleaning column and the formula that depends on it that refer to each other, causing the calculation chain to break. Copy the cleaning result to a numerical value, and then re-check the reference relationship.
# Summary
TRIM can only handle normal spaces and needs to be used in conjunction with SUBSTITUTE and CLEAN when encountering uninterrupted spaces, full-width spaces, or line breaks. First use LEN and CODE to find out the character types, then replace them symptomatically, and finally check the results, you can clean the Excel data more securely.
