Data validation conundrum - validating various combined comma separated values in single cell

[Nobby_the_Mouse]

Dear Excel brains of the world,

I have a data validation conundrum; spent an hour looking for a solution to no avail

I have the following list of values:

AR, ARNC, ARNF, ARSAP, B, C, E, ER, ES, N, T, V

To prevent user error, I need to be able to add a Data Validation rule into the columns' cells to allow any combination of the above to be successfully entered, separated by commas. Examples of successful entries could therefore be...

AR, B, V
ES, ARSAP, V, E, C, B
ES
ER, T

...you get the idea; I hope!

This is for a charity project to conserve reptile species, so your help would be much appreciated

Cheers,
Ben

 

[Cross Terrier]

I found this thread from ages ago, which may help: https://www.mrexcel.com/forum/excel-questions/587070-extracting-string-between-commas.html

However, it seems to me that it would be far simpler to only allow a single value to be entered at a time. Then you can just use a list to validate the data. You can add commas later with concatenation if they are required.

 

[MickG]

Have a look Here !!!
https://www.mrexcel.com/forum/excel...t-tried-macro-but-does-not-work-new-post.html

 

[oldbrewer]

AR, B is different to AR,B

is the space optional - and would a vba solution be acceptable ? (color red any that "fail")

 

[Nobby_the_Mouse]

Hi peoples,

Many thanks for speedy response... in answer to questions...

I do not wish to use a vba code as the spreadsheet already has a complicated macro in place.

Preferably I'd like a space to be present, but not essential; the choice of both would be the best option.

Does this help or hinder?

Cheers,
Ben

 

[Rick Rothstein]

I do not wish to use a vba code as the spreadsheet already has a complicated macro in place.

If you insist on that restriction, I do not believe you will get a solution to your problem. Besides, the VBA code you need is not a macro, rather, it would be an event procedure so that it can act just like data validation would... automatically without the need for the user to do anything. If you change your mind about using a VBA solution like I have outlined, post back with the range of cells that will need this data validation routine applied to them.