Data Validation Error for Typed Entry but not for Dropdown! Yes, I typed it right.

[Justin Bustin]

I am using data validation for a particular column. The category list of values is pulled in from a website into a hidden sheet. I did this because I may need to frequently change the allowed values for the category list.

The data pulls in correctly and does restrict--in the dropdown options, I am able to see any added category list item.

However, when I manually type in the field, it generates a data validate error:

"The value you entered is not valid."

Why the heck would it work for a dropdown but not for a manually typed in entry? I have checked extra cell spaces, cell format type, and so on.

 

[Scott T]

I would guess that the values pulled from the website have non printing characters, line non breaking spaces, in them that the data validation is looking for.

Try using clean to remove non printing characters and see if that works

https://exceljet.net/tips/how-to-clean-text-with-trim-and-clean

 

[Justin Bustin]

Thanks for the reply! Where would I enter in the CLEAN() or TRIM() functions? I tried in the data validate selection formula to no avail.

The connection pulls from a table in SharePoint into an unformatted "table" in the a sheet included in the workbook.

 

[Scott T]

I would try putting your current formula into clean

=CLEAN(your current fomula here)

 

[Justin Bustin]

Done. I pulled in the data from the website, and then referenced each cell with =TRIM(CLEAN(AX)) where X = 1, 2, 3, 4, etc.

Nothing--still the same error. I do have quite a few fields for the formula to look at. Is there a limit to the number of items that data validate can look at when it let's you type it in?

 

[Justin Bustin]

My guess is that this is unsolvable.

 

[Justin Bustin]

ALL RIGHT! Found a lead:

It's something with the first character. If I copy and paste the value it's data validating from, everything's okay. However, if I type the same data, I get an error. I replaced every letter until I found that the first letter of my name (J) is what's causing it to recognize. However, neither trim nor clean is fixing it.

Any ideas? I feel that I'm so close!!!

 

[Scott T]

Unfortunately clean does not remove all non printing characters or it could be a different problem.

Change to point to one of your data validation options and see what code is returned to see if that is the problem. copy down if need to test whole string.

Excel 2010
	A	B	C	D	E
1	test text here			116	t
2				101	e
3				115	s
4				116	t
5				32
6				116	t
7				101	e
8				120	x
9				116	t
10				32
11				104	h
12				101	e
13				114	r
14				101	e
15
Sheet1

Cell Formulas
Range	Formula
D1	=CODE(MID($A$1,ROW(D1),1))
D2	=CODE(MID($A$1,ROW(D2),1))
D3	=CODE(MID($A$1,ROW(D3),1))
D4	=CODE(MID($A$1,ROW(D4),1))
D5	=CODE(MID($A$1,ROW(D5),1))
D6	=CODE(MID($A$1,ROW(D6),1))
D7	=CODE(MID($A$1,ROW(D7),1))
D8	=CODE(MID($A$1,ROW(D8),1))
D9	=CODE(MID($A$1,ROW(D9),1))
D10	=CODE(MID($A$1,ROW(D10),1))
D11	=CODE(MID($A$1,ROW(D11),1))
D12	=CODE(MID($A$1,ROW(D12),1))
D13	=CODE(MID($A$1,ROW(D13),1))
D14	=CODE(MID($A$1,ROW(D14),1))
E1	=CHAR(D1)
E2	=CHAR(D2)
E3	=CHAR(D3)
E4	=CHAR(D4)
E5	=CHAR(D5)
E6	=CHAR(D6)
E7	=CHAR(D7)
E8	=CHAR(D8)
E9	=CHAR(D9)
E10	=CHAR(D10)
E11	=CHAR(D11)
E12	=CHAR(D12)
E13	=CHAR(D13)
E14	=CHAR(D14)

 

[Joe4]

First, let's find the ASCII code for the character in the first space. That will tell us what we are dealing with here.
If the entry was in cell A1, here is the formula to tell us that:

=CODE(LEFT(A1,1))

Once we get that number, we can look here to see what we are dealing with: https://www.asciitable.com/

 

[Justin Bustin]

You guys got it! Thank you so much, it was a weird, invisible leading character. Some artifact from SharePoint.