How can I get data validation to care about spaces?

[edeibold]

How can I get data validation to recognize the difference between [acceptable input] and [acceptable input][followed by a space]? The spaces obviously cause a huge amount of problems, is there a way I can get data validation to reject otherwise acceptable entries that are followed by a space?

 

[Z51]

"It depends". (Or "It depends " as the case may be.) What are you trying to validate and how, currently? Provide some examples.

In testing, using 'List' validation that includes names like Bob, Tom and Jim, it also allows values that include a space after the name. Another method would be to use 'Custom' validation, something like:

=OR(A1="Jim",A1="Bob",A1="Tom")

This way, only those exact values are acceptable.

 

[jasonb75]

You can't ignore spaces with a dropdown list unless you use vba to trim them.

 

[edeibold]

[I'm still learning how to use this site and I don't know how to delete a comment]

 

[edeibold]

You can't ignore spaces with a dropdown list unless you use vba to trim them.

I don't think that's true. The drop down menu was enabled for the cells that had the issue

 

[edeibold]

"It depends". (Or "It depends " as the case may be.) What are you trying to validate and how, currently? Provide some examples.

In testing, using 'List' validation that includes names like Bob, Tom and Jim, it also allows values that include a space after the name. Another method would be to use 'Custom' validation, something like:

=OR(A1="Jim",A1="Bob",A1="Tom")

This way, only those exact values are acceptable.

The data values I'm trying to validate are "Y" and "N". The settings I was using currently are allow list and the list is "Y,N" (no quotes in the actual list of course)

Can you be more specific about your example? I tried copying and pasting it into excel and it didn't seem to do anything (it allowed any value with the settings you suggested)

 

[Z51]

Let's say the range of cells that you want to restrict to Y or N are D5:D15. Select D5:D15, then in Data Validation select Custom and put the formula:

=OR(D5="Y",D5="N")

Click OK to close the Data Validation window. Try entering anything other than Y or N in D5:D15, including with spaces. It will only let you put the single character Y or N (or lower-case versions of them).

 

[jasonb75]

I don't think that's true. The drop down menu was enabled for the cells that had the issue

That is exactly what I said, if you're using a dropdown list then it will not ignore spaces at the end (it should but it doesn't).

If you want the dropdown then the easiest way to fix it would be a worksheet change event to remove the excess spaces at the time of entry. If you don't need the dropdown then the method shown by @Z51 will work just fine.

 

[FormR]

The settings I was using currently are allow list and the list is "Y,N"

Hi, you could try putting the Y and N into, says cells A1 and A2 of say a sheet called "hidden" and then use this as the "Source" for the "List" option:

=Hidden!$A$1:$A$2

You can then, obviously, hide that sheet if preferable.

 

[jasonb75]

you could try putting the Y and N into, says cells A1 and A2 of say a sheet called "hidden" and then use this as the "Source" for the "List" option:

I hadn't encountered the erroneous space issue with validation so tested the dropdown to see the problem and could have sworn that it happened with this method as well when I tried it.

I really must pay more attention to what I'm doing