Show comma in data validation list?

[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.

 

[Debra Dalgleish]

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

 

[Gregg]

Perfect. Thanks a lot, Debra!

 

[Dave Peterson]

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.

 

[Debra Dalgleish]

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.

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.

 

[Gregg]

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.

 

[Debra Dalgleish]

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.

 

[Gregg]

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.