About this articleThis article was generated using an automated generation workflow powered by generative AI. We reviewed the official Microsoft TRIM function and data cleaning methods, comparing visually similar characters using fictitious item codes. Input and calculation in actual Excel have not been verified.
Verification Status: 📘 Official information checked, actual Excel not verified
Excel TRIM not removing spaces? Fix mismatch errors with CHAR(160) and SUBSTITUTE
- Supported Environments, Capabilities, and Limitations (Official Information Checked: November 17, 2026)
- First, compare in a safe, new workbook.
- Why TRIM alone does not fix the issue
- What happens if you change only one condition
- Troubleshooting when things do not work
- Separate originals and conversion results for business use
- Official Information and Verification Scope
Supported Environments, Capabilities, and Limitations (Official Information Checked: November 17, 2026)
| Item | Target and Conditions |
|---|---|
| Target | Excel for Microsoft 365, Excel 2024/2021/2019/2016 (Applicable versions for official Microsoft TRIM function) |
| Operation | Enter fictitious data and formulas into cells in a new workbook. No macros, external connections, or administrator privileges required. |
| Capabilities | Distinguish between ASCII spaces and non-breaking spaces (160), target them explicitly, and replace them |
| Limitations | Display and calculations in actual Excel are unverified. Full-width spaces and other control characters are separate issues. |
| Safety | Create normalized results in a separate column without overwriting the original. Do not paste actual data into external services. |
When you paste product codes from a CSV file or a web screen, you may find that they look identical like "AB-12" but fail to match during comparison. One reason is that the spaces at the beginning and end are not standard half-width spaces, but rather "non-breaking spaces" commonly used on web pages. The TRIM function in Excel is not a universal space remover. According to Microsoft documentation, TRIM targets ASCII spaces with character code 32, and cannot remove space 160 on its own.
Knowing this difference allows you to inspect input values before rebuilding the entire formula when conditions in XLOOKUP or COUNTIF match but still fail to align. Below is a small reproduction example that does not use live data. The results are based on official specifications and are not measured values from an actual machine.
First, compare in a safe, new workbook.
Open a blank Excel workbook, and enter "Source Data" in A1, "TRIM Only" in B1, and "Replace 160 with TRIM" in C1. Enter the following formula in A2.
=" "&"AB-12"&" "
Enter the following formula in A3.
=CHAR(160)&"AB-12"&CHAR(160)
A2 is a text string with standard half-width spaces on both ends. Even though A3 looks similar, both ends contain character code 160. Generating the characters using CHAR allows you to fix the experimental conditions more reliably than directly copying invisible characters from a web page. Note that the displayed whitespace width may vary depending on your environment.
Enter =TRIM(A2) into B3, and =TRIM(A3) into B2. Enter the following respective formulas into C2 and C3.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
=TRIM(SUBSTITUTE(A3,CHAR(160)," "))
SUBSTITUTE replaces a specified string with another string. Here, character 160 is temporarily converted to a standard half-width space, after which TRIM removes the leading and trailing spaces. The reason for converting it to a half-width space instead of deleting it is to prevent words from being awkwardly joined together even if character 160 appears in the middle of a sentence.
| Cell | Expected Content | ="AB-12"Comparison with |
|---|---|---|
| B2 | AB-12 | Expected to match |
| B3 | AB-12 with code 160 remaining on both ends | Expected not to match |
| C2 | AB-12 | Expected match |
| C3 | AB-12 | Expected match |
You can verify the comparison by entering into D2 =B2="AB-12", into D3 =B3="AB-12", and into E3 =C3="AB-12". The expected values are TRUE, FALSE, and TRUE, in that order. The key is to observe the logical values, not just the visible characters on the screen.
Why TRIM alone does not fix the issue
The Excel TRIM function removes leading and trailing standard half-width spaces and collapses consecutive standard spaces between words into a single space. However, it is not a function that deletes all Unicode characters that appear to be blank. Microsoft's official documentation explicitly lists character 160 as an exception.
The important point here is to separate visual appearance from internal data. Matching functions do not use whether things look the same width on screen, but rather the text string held by the cell. An item code containing character code 160 and an item code without any attached characters are distinct values. Simply adding IFERROR to the original matching formula to suppress error displays does not resolve the data mismatch.
When you want to inspect it, you can also try using =CODE(LEFT(A3,1)) for the leading character. In this experiment, 160 is the expected return value. When multiple types of invisible characters are mixed into an actual CSV, checking only the first character is insufficient. Check for trailing characters, characters in the middle, line breaks, tabs, full-width spaces, and more.
What happens if you change only one condition
Let us replace both CHAR(160) in the formula for A3 with CHAR(32). This changes A3 into a string with normal half-width spaces at both ends. You can predict that even just =TRIM(A3) in B3 will result in "AB-12". The formula in C3 remains unchanged. Because the only changed condition is the "type of space," you can compare the boundaries where TRIM takes effect.
Conversely, changing A3 to =UNICHAR(12288)&"AB-12"&UNICHAR(12288) will now add full-width spaces. In this CHAR(160)Dedicated replacements do not target full-width spaces. Instead of blindly using a formula that deletes all spaces at once, you need to decide which characters to normalize. In product or personal names, removing meaningful spaces creates new problems.
Troubleshooting when things do not work
If the formulais displayed as-is, check whether the cell is formatted as text or if it starts with an apostrophe.If it does not evaluateto the expected TRUE, first check whether other spaces or line breaks are mixed in before and after the source data. If adding the replacement formula to column C causes no change, verify whether column A actually contains 160. If the character type differs, replacing only 160 will have no effect.
If the character code is not 160,refer to the official data cleanup documentation and combine CLEAN, SUBSTITUTE, etc., restricted to the necessary range. CLEAN is not a universal Unicode removal function. Before uniformly deleting items including non-space characters, agree with the business side on what should be retained.
If searches or aggregationsare not fixed, also check whether the formulas reference the normalized column C. Continuing to reference the original column A means search results will not change even if clean values are generated in column C. When reconciling both tables, normalizing only one side may still leave notation inconsistencies.
Separate originals and conversion results for business use
In customer ledgers, part codes, and order lists acquired from the web, mixed-in spaces lead to duplicate judgments and master matching failures. However, avoid overwriting the original columns. Keep three columns: "Original", "Normalized Result", and "Change Status",=A2=C2and apply changes after understanding the differences using features such as those. Also check the number of items integrated into the same code through normalization to ensure identifiers do not unintentionally duplicate.
First, verify the formulas and results with a few dummy rows, then validate reconciliation differences using a small volume of copied business data. Design the setup so that any issues can be reverted to the original data simply by stopping the reference to the normalized column. The goal is not merely to remove character code 160, but to ensure that the same items are treated as the same values in business operations.
Official Information and Verification Scope
Microsoft Support: TRIM Function— Differences between standard half-width spaces and 160
Microsoft Support: Data Cleanup— Proper use of TRIM, CLEAN, and SUBSTITUTE
The contents of the official documentation were verified on November 17, 2026. Execution of the listed formulas in actual Excel environments, display differences between Windows and Mac, and reconciliation with actual business data have not been verified.
