Show comma in data validation list?

  • Thread starter Thread starter greggbeck2000
  • Start date Start date
G

greggbeck2000

Would someone please help. Thanks.

I want the following 3 items in a data validation list. Note they have
commas in the items.

Apple, Kiwi, and Orange
Strawberry, Mango, and Banana
Pineapple, Banana, and Orange

I can't get data validation to display the comma in the item because it
takes the comma as a seperator for a new item. So typing the above show
the following list:

Apple
Kiwi
and Orange
Strawberry
Mango
and Banana
Pineapple
Banana
and Orange

I have to be able to type these items directly into the DV list, not
into cells where DV uses a cell reference.

Can this be done?

Thanks again.
 
In the Data Validation dialog box, for the commas that you want to
appear in the item, hold the Alt key, and on the number keypad, type: 0130
 
You are a cheater <vbg>.

That's not really a comma.

And if the user always chooses the value using the dropdown, then this won't
matter. But if the user types in a valid entry, the user will have to type in
that alt-0130 instead of a comma.

Or if there's a formula that refers to that cell, it can't use the real comma.
 
I prefer the term "creative" <g>.

But you're right about the other stuff -- if no formulas are based on
the cell contents, the error alert could be turned off, and the values
could be typed with a real comma.

Or, programming could convert the fake commas to real commas.

Dave said:
You are a cheater <vbg>.

That's not really a comma.

And if the user always chooses the value using the dropdown, then this won't
matter. But if the user types in a valid entry, the user will have to type in
that alt-0130 instead of a comma.

Or if there's a formula that refers to that cell, it can't use the real comma.
Click to expand...
 
Yup, I found that out a bit ago. ;-)

We use the commas as delimiters in a search routine. So, I'll either
extend the code to include the alternative comma as delimiters or write
an event that changes the alt-commas to true commas.

Thanks to both of you for your help.
 
In your first post, you said you had to use a delimited list, but could
you use a named list on a hidden worksheet? Then, you could include the
commas, and not have to worry about programming.
 
Yeah, I thought about that early on, but I wanted to avoid using
another area.

My simplest solution so far is to abandon commas alltogether and us "/"
instead. So and item would look like this...

Apple / Orange / Pear

Luckily none of my items include that delimiter. I can then use that
character easily in search and count routines and the user can easily
edit the DV list directly in the dialog box.
 
Back
Top