Forum Discussion
Specifying manually entered data validation list entries as "Text"
I have manually entered a data validation list directly into a cells Data Validation input box. The list contains the following items I've hand-typed into the list: 7/23, 5/1, 7/1, 10/1, 5/25.
The cell is formatted as "General". I have tried "Text" as well.
However, when I use the pull-down within the cell it thinks my entries are "Dates". The pull-down list shows my options as "Jul-23, May-1" etc. Other times - no matter what formatting I choose it actually does the math and shows 7 divided by 23.
Choosing an option also seems to changes the formatting of the cell to "Date" format.
How do I indicate in the Source input field that my values are "Text"? I tried quotation marks, but then the quotation marks show up in my drop down, which I don't want. I need it to show only 7/23 with no quotes.
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_Lynn I think you were on the right track. Using the concatenate with null (&"") at the end of your formula will force everything to text.
=B2&"/"&C2&""That particular response was referring to a list created by referencing cells. This particular thread is specifically talking about manually entering a list directly into the Source input field in the Data Validation dialog box.
Jaime_Lynn My apologies for not reading closely enough.
You can try concatenated values like in range A2:A10 in this example. The formula in A2 is:
=B2&"/"&C2I 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.
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
