I have a data validation issue.
My cell reference should be 2 letters then 4 digits then 1 letter
As Example - AB1234A, xY0123z
Need to consider Both simple and Capital as well. Other way of input need to reject.
CodePudding user response:
Use custom validation formula for that cell.
Check if first 2 are letters with ISTEXT
and LEFT
formulas. If 3rd, 4th, 5th and 5th are numbers with ISNUMBER
and MID
formulas, check if length is 7 symbols with LEN
and so on. Wrap everything with AND
And you will get something like this:
=AND(LEN(C4)=7,ISTEXT(LEFT(C4,2)),ISNUMBER(VALUE(MID(C4,3,4))),ISNUMBER(RIGHT(C4,1) 1)=FALSE)
(Note that if you extract 1 symbol with LEFT
, RIGHT
or MID
and it is a number it will be treated like text. Workaround is to add any number and check if it converts to a number (adding number to actual text would give value error)