The match is failing on a character you cannot see.

In Excel, INV-10482 and INV-10482 look identical in two CSV exports, but VLOOKUP, XLOOKUP, MATCH, and exact filters do not compare what the cell looks like. They compare the full stored value, including the blank at the end.

If trailing spaces in CSV exports break spreadsheet matching in your month-end file, the fix is not to keep retyping formulas. The fix is to identify which export carries the hidden character, confirm whether it is a regular space or something worse, and clean the right side without damaging valid text.

This shows how to diagnose that problem quickly, fix it inside the formula when you can, and build a safer cleanup layer when the same files keep coming back.

What the failure looks like before you touch the file

This is why the problem feels irrational. The visible value looks correct, but the stored value is not the same on both sides.

The fifth row matters most. A normal trailing space is one problem. A non-breaking space is another. They look the same in the grid, but they do not behave the same in Excel.

Where the extra character usually comes from

CSV files do not invent spaces on their own. The extra character usually starts in the source field, then survives the export.

The common sources in finance files are predictable:

• A legacy system stores a text field in a fixed width and pads the value to the field length before export.
• A browser-based back office saves a copied value that includes a non-breaking space from the page layout.
• A user pastes a payout reference, customer code, or ledger ID with a blank at the end, and the system preserves it.
• One platform trims on export while the other preserves raw entry exactly as stored.

That is why the problem shows up so often in payout IDs, bank references, customer codes, invoice numbers, and ledger references. These are exact-match fields. One extra character is enough to make a correct record look unmatched.

Trailing spaces also create false confidence because they often affect only one side of the comparison. One export looks clean. The other looks clean too. The mismatch only appears when the formula compares them directly.

If the files also use different labels for the same field, trimming will not solve that part. That is a separate mapping problem covered in bank CSV files with different column names.

Prove which side is dirty before you clean anything

Do not clean both files blind. First prove what is different.

Use these checks on the value from File A and the matching value from File B:

Run those formulas on both sides of the match key. That tells you which file needs attention.

For example:

• If LEN(A2) is 8 and LEN(B2) is 9, the extra character is on the B side.
• If TYPE(A2) returns 1 and TYPE(B2) returns 2, trimming alone will not finish the job because you also have a type mismatch.
• If CODE(RIGHT(B2,1)) returns 160, the value contains a non-breaking space and plain TRIM() is not enough.

This matters because different failures need different fixes. A padded text ID is not the same as a text-vs-number mismatch. A text date with a trailing blank is not the same as a real Excel date.

That broader pattern is what sits behind mismatched CSV formats: the values may represent the same thing, but the files did not store them the same way.

Fix the match inside the formula first

If you can solve the mismatch inside the lookup formula, do that before you start rebuilding columns.

When the lookup value itself has the trailing space

Wrap the lookup value in TRIM():

=VLOOKUP(TRIM(A2),$D$2:$E$1000,2,FALSE)

=XLOOKUP(TRIM(A2),$D$2:$D$1000,$E$2:$E$1000,"",0)

That works when the dirty value is the one in A2 and the lookup table itself is clean.

When both sides may contain normal trailing spaces

Clean both sides inside the comparison:

=XLOOKUP(TRIM(A2),TRIM($D$2:$D$1000),$E$2:$E$1000,"",0)

This is often the fastest fix when you are working in current Excel and the problem is standard space padding.

When the real problem is `CHAR(160)`

CHAR(160) is the non-breaking space character. It often comes from browser-based exports or values that were copied out of web tables. It looks like a normal trailing blank, but Excel's TRIM() does not remove it by itself.

Strip it explicitly with SUBSTITUTE() before trimming:

=XLOOKUP(
  TRIM(SUBSTITUTE(A2,CHAR(160),"")),
  TRIM(SUBSTITUTE($D$2:$D$1000,CHAR(160),"")),
  $E$2:$E$1000,
  "",
  0
)

If you only clean A2 but the lookup array still contains CHAR(160), the match still fails. Clean the side that actually carries the character.

When the file has a type mismatch as well as the trailing space

Suppose one file stores 9120 as text and the other stores 9120 as a number. Remove the space, then coerce the value to the correct type in the same step.

If the key should be numeric and leading zeros are not meaningful:

=XLOOKUP(VALUE(TRIM(A2)),VALUE(TRIM($D$2:$D$1000)),$E$2:$E$1000,"",0)

If the key should stay as text because leading zeros matter, standardize both sides as text instead:

=XLOOKUP(TEXT(TRIM(A2),"0"),TEXT($D$2:$D$1000,"0"),$E$2:$E$1000,"",0)

Use VALUE() only when 0004517 and 4517 are supposed to be the same key. If the zeros are part of the real reference, keep the field as text.

When the trailing space sits on a text date

Strip the blank first. Then convert deliberately.

For an ISO-style text date such as 2026-05-01 :

=DATE(LEFT(TRIM(A2),4),MID(TRIM(A2),6,2),RIGHT(TRIM(A2),2))

For a value like 01/05/2026 , stop before converting until you know whether the export means day/month/year or month/day/year. That ambiguity matters more than the trailing space. Clean the text first, then parse the date according to the source system's actual format.

Do not use Find and Replace to remove every space across a column unless the field is an ID that should never contain spaces at all. On free-text descriptions, customer names, and bank narrations, that approach destroys valid data.

When a cleanup column is the safer workflow

Inline fixes are good for one formula. They become harder to control when the same match key feeds a full workbook.

If the file is large or reused every month, create a working cleanup layer and point every match to that layer instead of the raw export.

That structure does three useful things:

• It preserves the raw file so you can prove what arrived from the source.
• It makes your cleanup rules visible instead of burying them inside every formula.
• It stops one dirty export from forcing different fixes into five different tabs.

This is also the point where you catch secondary problems that trimming cannot solve. If the cleaned keys still do not match, the next likely causes are:

• the wrong match field
• duplicate references on one side
• a date/number/text format mismatch
• different column naming between the two files

That is why a single TRIM() fix sometimes works immediately and sometimes only reveals the next problem underneath it.

When repeated CSV cleanup stops being the real work

One bad export is a quick repair. The fifth bad export is a workflow problem.

If every reconciliation means importing two files, checking LEN(), hunting for CHAR(160), coercing text into numbers, and rebuilding exact-match formulas, the time is no longer going into reconciliation judgment. It is going into file cleanup.

That is the point where trailing spaces stop being a cell-level nuisance and start being the reason a one-hour check turns into an afternoon.