SOLVED

Specifying manually entered data validation list entries as "Text"

Copper Contributor

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.

 

pulldown issue.png

10 Replies

@Jaime_Lynn 

dropdown list.JPG

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.

 

pulldown issue.png

best response confirmed by Jaime_Lynn (Copper Contributor)
Solution

@Jaime_Lynn 

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

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.

@Jaime_Lynn 

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.

Your responses were much appreciated. Thank you!

@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&""

 

DexterG_III_0-1662063488771.png

 

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.  

1 best response

Accepted Solutions
best response confirmed by Jaime_Lynn (Copper Contributor)
Solution

@Jaime_Lynn 

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

View solution in original post