Data validation list with a comma

[DashboardNovice]

My list values are comma separated. But suppose I want one of my list items to include a comma such as:

Bangalore, India

I tried enclosing this in " " but the quotes will appear in the pull down menu. If I type this as is, Bangalore is recognized as one item then India is recognized as another item. But I want Bangalore, India to be one item list.

Also, suppose I want a blank as a choice, just in case the user doesn't want to select from the list of options. How would I get a blank as a choice?

I know how to do these by referencing a named range, but I would like all of my entries to be hard coded into the DV list.

 

[eibi]

I suggest that you type your list on a separate worksheet. Then, name the range "CityList".

When you need the list (for your data validation, for example), simply point it to the Named Range.

=CityList

I think that this will allow you to use commas without any problem...

See attached sample.

All best.

 

[eibi]

DN,

I noted that you edited your original post to indicate that you prefer to hard code the list. If you want your questions to receive fresh attention, I'd recommend that you reply to the thread rather than changing the original post...That way the forum realizes a new comment has been posted, and the discussion gets raised back to the top of the board.

All best.

 

[eibi]

Here's a "cheat" for the comma that you want:

When you hard code the data validation list, instead of a comma in your entry (Boston, MA) , use this key combination: Alt+0130

It will display a comma in the data validation list, as you requested, BUT, it isn't a "real" comma, so it will be ignored if you search for the string - Boston, MA - in the other parts of the spreadsheet...Or if you try to use the ouput of the data validation selection to lookup matching values elsewhere in your workbook...

But if you are hard-coding your data validation list, I assume that you aren't planning to use it to reference other places in the workbook, right?

 

[DashboardNovice]

Here's a "cheat" for the comma that you want:

When you hard code the data validation list, instead of a comma in your entry (Boston, MA) , use this key combination: Alt+0130

It will display a comma in the data validation list, as you requested, BUT, it isn't a "real" comma, so it will be ignored if you search for the string - Boston, MA - in the other parts of the spreadsheet...Or if you try to use the ouput of the data validation selection to lookup matching values elsewhere in your workbook...

But if you are hard-coding your data validation list, I assume that you aren't planning to use it to reference other places in the workbook, right?

Interesting solution for that comma. I'll keep your suggestion in mind about replying.

 

[Dalia]

for me its not happening