Show comma in data validation list?

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

[email protected] wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Perfect. Thanks a lot, Debra!

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 wrote:
>
> 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
>
> [email protected] wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

--

Dave Peterson

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 Peterson wrote:
> 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 wrote:
>
>>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
>>
>>[email protected] wrote:
>>
>>>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
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

Gregg wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.