Statement working in a cell but not in data validation

[gerotutu]

I need to check if the field that is completed in column W is a number of at least 7 characters. I cannot allow anything different, except when in column V is "TARJETA_CREDITO".

In the case the field in column V is "TARJETA_CREDITO" the previous validation needs to be changed to XOR you may see below (they are some basic rules to validate different types of credit cards).

I tried this formula out in a normal cell and it works but it does not work properly when I use it as data validation. As data validation it only works when selecting "TARJETA_CREDITO".

The basic idea would be: IF V=TARJETA_CREDITO THEN Check W is a credit card number ELSE Check W is a number and with a length of at least 7 characters.

=IF(V3="TARJETA_CREDITO";
AND(ISNUMBER(VALUE(W3));XOR(AND(LENGTH(W3)=14;LEFT(W3;2)="36");AND(LENGTH(W3)=16;OR(LEFT(W3;1)="5";LEFT(W3;1)="4"));AND(LENGTH(W3)=15;LEFT(W3;2)="37")));
AND(LENGTH(W3)>=7;ISNUMBER(VALUE(W3)))
)

The second path of the IF never works in data validation. I don't get why! Hope you can help me...
Thanks, again!

 

[gerotutu]

I translated wrong the formula to the English syntax

=IF(V3="TARJETA_CREDITO",
AND(ISNUMBER(VALUE(W3)),XOR(AND(LEN(W3)=14,LEFT(W3,2)="36"),AND(LEN(W3)=16,OR(LEFT(W3,1)="5",LEFT(W3,1)="4")),AND(LEN(W3)=15,LEFT(W3,2)="37"))),
AND(LEN(W3)>=7,ISNUMBER(VALUE(W3)))
)

 

[gerotutu]

I just unmark the option to "Omitir blancos" ("Skip blanks") and it started working. I had yesterday all the day lost because of this. Don't know now how exactly that option works.


Lesson learnt. Thanks,
Gerónimo