cells validation

[akika]

hello,
Which validation can i use for a cell to accepts as input format "M <space> 123456"
e.g M 123456
should always start with M followed by space and 5 to 6 numbers

in custom validation ive tried .. wht else should i include to meet the criteria??
=EXACT(LEFT(A3,1),"M ")

 

[GraH - Guido]

Hi, "M " is 2 positions, and you only check for 1 in your formula LEFT(A3,1)

perhaps this might work
=AND(LEN(E3)<9,LEFT(E3,2)="M ",ISNUMBER(VALUE(MID(E3,3,6))))

 

[bosco_yip]

Here's my formula solution

In B2, copied down :

=AND(AND(LEN(A2)>6,LEN(A2)<9),EXACT(LEFT(A2,2),"M "),ISNUMBER(MID(A2,3,6)*MID(A2,3,1)))

Regards
Bosco