Using data validation to set naming conventions

I'm trying to use the data validation to make sure people use the same format when entering product codes into a s/sheet.

The product codes are 5 numeric digits teh letter 'v' then one or more numeric digits again.
eg
12345v1
12345v12
16549v3

Is there a way to use custom validation to make sure that the text entered follows this convention?

Thanks for any help you can offer.

Hi,

Try: =AND(LEN(A1)=7,ISNUMBER(LEFT(A1,5)*1),MID(A1,6,1)="v",ISNUMBER(RIGHT(A1,1)*1))

Dom

Hi,

I think this gives you what you want

=AND(ISNUMBER(VALUE(LEFT(A1,5))),MID(A1,6,1)="v",ISNUMBER(VALUE(RIGHT(A1,LEN(A1)-FIND("v",A1)))))

Thanks a lot for your help guys.
Sweep yours works perfectly, Domski thanks for your help but yours does'nt allow codes with 2 digits after the v only one it seems.

Sweep yours works a treat thanks for such a quick response from you both
:D

Another variant

Please Login or Register  to view this content.

I was trying to come up with a way of avoiding the search altogether - ie just LOOKUP - but couldn't (roundings etc) - any bright ideas ?

Thanks for trying but according to excel:

You may not use unions, intersections, or array constants for Data Validation criteria.

Nevermind, I'm happy with Sweeps version, but thanks for having a go

Sorry - yes - quite right - there's no blush icon here unfortunately... helps to have the brain turned on (and test) I guess.

One last quick question!

I've set Input Messages on the column headers to explain what needs entering where, but when you select the cell, the messages all appear in the sam place, and not next to the selected cell, if I move it, then all the messages open in that place, not just the one I moved.

Can this be changed or is it something I just need to live with?
(If I cant change it, I'll go back to using comments!)

Thanks

:D