Need formula for Data Validation

[Aswinraj]

Hello Friends,

I need your help in finding out the formula for Data Validation.


I have 2 Columns (A,B) - Need formula to validate the Serial Number.
Formula condition should be Column A Should always starts with "S" and after 5 Digits it should contain "ID" and after 3 digits it should contain "W" (Or Last 4 Digit should start with "W")


Column A Column B

S26412ID469W1820

Please help me with the Formula.

With Regards,
Aswinraj A

 

[RoryA]

Does it matter whether it's "W" or "w", or "S" or "s"?

 

[Aswinraj]

Hi Rory,

"S" or "s" it doesn't matter. (Either Small or capital)



With Regards,
Aswinraj A

 

[Lukas C]

Hi hope u got it sorted if not here is this
=IF(LEFT($A1,1)="s",1,0)+IF(MID($A1,7,2)="id",1,0)+IF(MID($A1,12,1)="w",1,0)
it everything is correct it = 3, if only 2 correct =2 then u can get condition format color on cells b to go green (or whatever color) if =3

 

[Aswinraj]

Hi hope u got it sorted if not here is this
=IF(LEFT($A1,1)="s",1,0)+IF(MID($A1,7,2)="id",1,0)+IF(MID($A1,12,1)="w",1,0)
it everything is correct it = 3, if only 2 correct =2 then u can get condition format color on cells b to go green (or whatever color) if =3

Hi Bro,

Thanks a lot for your help

Yeah i too find out another formula "=IF(AND(LEFT(T2,1)="S",MID(T2,7,2)="ID",MID(T2,12,1)="W"),"True","False")"

 

[RoryA]

You don't need the IF since AND will return either True/False:

=AND(LEFT(T2,1)="S",MID(T2,7,2)="ID",MID(T2,12,1)="W")

although just be aware that that won't check for digits in between.

 

[Aswinraj]

You don't need the IF since AND will return either True/False:

=AND(LEFT(T2,1)="S",MID(T2,7,2)="ID",MID(T2,12,1)="W")

although just be aware that that won't check for digits in between.

Ok RoryA - I understand your formula now. I need a small change in this formula,

MID(T2,12,1)="W" - This might be either W or X, how to incorporate this change?

 

[RoryA]

You could do this:

=AND(LEFT(T2,1)="S",MID(T2,7,2)="ID",OR(MID(T2,12,1)={"W","X"}))