Data validation doesn't work

Hello fellow excelpeople,

I am currently working with Excel 2010 and i would like excel to give me a warning when there are LESS or MORE than 18 signs (IBAN bank account number) in one cell.
I have set this up using data validation. However, it sometimes gives a warning even though there are 18 numbers in the cell (which is good)
Does anyone knows why this happens? Maybe because of the fact that a bank account number exists of numbers AND letters?

How do i fix this?

Thanks for your help!

Suzan

Hello Suzan welcome to the forum.

I'm not quite sure what you mean by '18 signs'. Is the length and syntax of each account number cell consistent? Is it: 'IBANxxxxxxxxxxxxxxxxxx' (18 numbers)?

DBY

Hello DBY,

First of all, thanks for your reply

It should look like this: AA11AAAA111111111
A standing for a random letter from the alphabet
1 standing for a random number
This is a European bank account number.

A real example would be; NL12INGB876498756

They should all have the same build up. If anything is not like the above, i want excel to give me a warning

Suzan

So it's: 2 letters; 4 numbers; 4 letters and 9 numbers consistently? If so, then there might be a Custom Validation formula that would work but I'll have to give it some thought. In the meantime perhaps some other forum member might have a solution.

Hello
If you can create a named range of numbers 0 to 9, perhaps called 'Numbers' then try this rather convoluted Custom Validation formula:

Formula:

=(MIN(FIND(Numbers,LEFT(A3,2)&1234567890))>2)*(ISNUMBER(VALUE(MID(A3,3,2))))*(MIN(FIND(Numbers,MID(A3,5,4)&1234567890))>4)*(ISNUMBER(VALUE(RIGHT(A3,9))))*LEN(A3)=17

Here applied to A3. This should produce an input error if the syntax described (2 letters; 4 numbers; 4 letters and 9 numbers; length 17) is not entered in the cell. It seems to work but you'll need to test it out and try and break it to see if works for you.

There may be a simpler way but at the moment it escapes me.

DBY