Fix Excel Data Validation Rejecting Manually Typed Codes
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.
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.
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.
Open the Entry Codes sheet and check the entries in Q2:Q138. Compare a rejected typed code with its source entry for extra spaces.
In an empty cell such as R2, enter =TRIM(CLEAN(SUBSTITUTE(Q2,CHAR(160)," "))) and fill the formula down through R138.
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.
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.
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.

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.

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.




