Sep 01 2022 10:36 AM - edited Sep 01 2022 12:23 PM
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.
Sep 01 2022 11:18 AM
You can try concatenated values like in range A2:A10 in this example. The formula in A2 is:
=B2&"/"&C2
Sep 01 2022 11:34 AM - edited Sep 01 2022 11:36 AM
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.
Sep 01 2022 12:04 PM
SolutionI'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
Sep 01 2022 12:21 PM - edited Sep 01 2022 12:22 PM
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.
Sep 01 2022 12:58 PM
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.
Sep 01 2022 01:14 PM
Sep 01 2022 01:34 PM
@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&""
Sep 01 2022 01:46 PM - edited Sep 01 2022 01:47 PM
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.
Sep 01 2022 02:09 PM
@Jaime_Lynn My apologies for not reading closely enough.
Sep 01 2022 02:53 PM
Sep 01 2022 12:04 PM
SolutionI'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