• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Validating data with a list (formatting list question)


New Member
Happy new year to all, especially Chandoo with his "upgraded" familly :)

I want to allow the user to select a value in a list for a range of cells, but I want the list to be displayed the following way :

CODE1 - Explanation 1

CODE2 - Explanation 2


Codex is the value in the cell.

Is there a way (formula / vb?) to do so ?

Cyril Z.

I would add a helper column and concatenate the Code and Explanation together

Assuming Code is in Column A

and Explanation is in Column B

=+CONCATENATE(A2," - ",B2)
Thanks you Hui,

This will lead to have the full value (code + explanation) in the cell.

I want only the code in the cell without adding an helper column.

I was wondering if this exist in Excel (even VBA).


Would you be able to give a slightly more detailed explanation of what you are trying to acheive?

have a look at

DV0005 - Data Validation "Columns" at the bottom of


That has an example that does what you want
For all interested, there is a side effect of "cutting" in the cell values returned from a validation list.

You cannot define the width of the drop-down list, which is really annoying with long names.

Is there any way to define this width and also the number of item displayed ?

Cyril Z.
@Cyril thanks for the wishes. Wish you a good year ahead.

Coming to your

Is there any way to define this width and also the number of item displayed ?
you can try form controls or even active-x controls. You can define width and number of items shown easily. Doesnt require any macros. Also you can link the output to a cell. but output will always be a number indicating the position of the selection in the list. You can mash this with vlookup to show CODEx.

For more fancy solutions you can resort to vba...