Forum Discussion
Specifying manually entered data validation list entries as "Text"
- Sep 01, 2022
I'd strongly recommend entering the list in a range; that is much easier to maintain.
But if you really prefer to specify it in the Source box, prefix each value with an apostrophe:
'7/23,'5/1
You can try concatenated values like in range A2:A10 in this example. The formula in A2 is:
=B2&"/"&C2
I really appreciate the quick response. I am not using a range of cells. I am using a manually entered list.
I am trying to figure out what I can input into the cells Data Validation input box to indicate that the values that I've typed in by hand are "Text" and not numbers or dates. At times it does the math and divides and other times it changes the options listed to "Dates"
I have been searching for an advanced description of the capabilities within the input box itself.
- HansVogelaarSep 01, 2022MVP
I'd strongly recommend entering the list in a range; that is much easier to maintain.
But if you really prefer to specify it in the Source box, prefix each value with an apostrophe:
'7/23,'5/1
- Jaime_LynnSep 01, 2022Copper Contributor
Thank you. I tried the apostrophe as well and, unfortunately, it shows the apostrophe in the pull down.
For now, of course my goal is to learn to indicate that the number values I've entered are "Text" values. But I wonder what other capabilities exist in that input field. For example, I found a tip that a # after an individual cell reference will choose the "entire list" below that cell however much it may expand or shrink.
Do you know of any comprehensive description of what can be accomplished in the data validation input field? I haven't been able to find anything beyond the basics. Perhaps something of that nature would contain my answer and give me more insight on what else can be accomplished there.- HansVogelaarSep 01, 2022MVP
Yes, the apostrophe will show up in the drop down list, and also in the formula bar after you select an item, but not in the cell itself.
If you don't like, that, you should really consider using a range as Source.
I'm afraid I don't know of a comprehensive reference of ticks and tricks for the Source. I do know that if you enter the list directly, you're limited to a total of 255 characters. You don't have this problem if you specify a range.