Function Problems

Fix Excel Data Validation Rejecting Manually Typed Codes

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

Question details

The user needs Excel to accept valid codes typed manually into cells with list-based data validation.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A cell uses an approved code list as its data validation source.
Observed behavior
Typing a code may be rejected when the matching source entry contains trailing spaces or hidden characters. Selecting the entry from the drop-down can work because it inserts the exact source value.
Before you start

Select an affected cell and check Data > Data Validation to identify its source list. Confirm whether spaces are meaningful in any of the approved codes and whether the source cells contain values or formulas.

Solution 1Recommended

Clean the Excel data validation source list

Use a helper column to remove common hidden characters and extra spaces before testing typed codes again.

The example below uses Entry Codes!Q2:Q138 as the source range. Replace these references with your actual list and an empty helper column if needed.

1
Inspect the source list

Open the Entry Codes sheet and check the entries in Q2:Q138. Compare a rejected typed code with its source entry for extra spaces.

2
Create cleaned entries

In an empty cell such as R2, enter =TRIM(CLEAN(SUBSTITUTE(Q2,CHAR(160)," "))) and fill the formula down through R138.

3
Review the results

Compare the cleaned entries in column R with the original codes in column Q. TRIM also reduces repeated internal spaces to one, so do not use the results if those spaces are significant.

4
Update the source values

If Q2:Q138 contains fixed values, copy R2:R138, select Q2, and use Paste Special > Values. If the original list contains formulas, adjust those formulas instead of overwriting them.

5
Test manual entry

Return to an affected cell, type a cleaned code such as AB, and press Enter. Check Data > Data Validation again if the entry is still rejected.

Clean the Excel data validation source list
Why the drop-down can work: Selecting from the drop-down inserts the exact list entry, including any trailing spaces. The formula removes common control characters and nonbreaking spaces, but other hidden characters may require further inspection.
Free Microsoft Office alternative

Work with code lists in WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar interface. Open compatible Excel workbooks and work with code lists and data validation as you migrate between applications; it does not repair Microsoft Excel itself.

Work with spreadsheet code lists and data validationCompatible with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet interface for an easier transitionFree and lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the drop-down accept a code that Excel rejects when I type it?

The source list may contain an extra space or hidden character. The drop-down inserts that exact value, while a manually typed code may not match it.

Where can I find the list used for data validation?

Select the affected cell and open Data > Data Validation. Check the Source field for the range, named range, or list used by the rule.

Can I use Find and Replace to remove spaces from the list?

Yes, if no code should contain any spaces. Select only the source list, press Ctrl+H, enter one space in Find what, leave Replace with blank, and choose Replace All. This removes internal spaces as well as trailing spaces.

What if the validation source contains formulas?

Do not paste cleaned values over formulas you need to keep. Edit the source formulas to produce clean codes, then test a typed entry in the validated cell.