alpha numeric format with dashes

[taliapaw]

Hi I'm trying to obtain this format TT-T#-T# and I'm using this formula but when I tested it failed to follow the pattern in text and numbers, it only kept the 2 dash 2 dash 2 format.

=AND(ISTEXT(LEFT(F1,2)),MID(F1,3,1)="-",ISTEXT(MID(F1,4,1)),ISNUMBER(MID(F1,5,1)*1),MID(F1,6,1)="-",ISTEXT(MID(F1,7,1)),ISNUMBER(MID(F1,8,1)*1),LEN(F1)=8)

Any suggestions or help would be greatly appreciated, thank you in advance.

 

[István Hirsch]

If this is a validation, test this formula:

=IF((LEN(F1)=8)*(LEFT(F1,1)>="A")*(MID(F1,2,1)>="A")*(MID(F1,3,1)="-")*(MID(F1,4,1)>="A")*AND(MID(F1,5,1)<"A",MID(F1,5,1)<>"-")*(MID(F1,6,1)="-")*(MID(F1,7,1)>="A")*AND(MID(F1,8,1)<"A",MID(F1,8,1)<>"-")=1,"correct","incorrect")

 

[István Hirsch]

if this is a validation, test this formula:

=if((len(f1)=8)*(left(f1,1)>="a")*(mid(f1,2,1)>="a")*(mid(f1,3,1)="-")*(mid(f1,4,1)>="a")*and(mid(f1,5,1)<"a",mid(f1,5,1)<>"-")*(mid(f1,6,1)="-")*(mid(f1,7,1)>="a")*and(mid(f1,8,1)<"a",mid(f1,8,1)<>"-")=1,"correct","incorrect")

sorry, the formula still needs correcting! Do not use it!

 

[Rick Rothstein]

sorry, the formula still needs correcting! Do not use it!

Maybe this...

=NOT(ISNUMBER(0+LEFT(A1,1)))*NOT(ISNUMBER(0+MID(A1,2,1)))*NOT(ISNUMBER(0+MID(A1,4,1)))*NOT(ISNUMBER(0+MID(A1,7,1)))*(MID(A1,3,1)&MID(A1,6,1)="--")*ISNUMBER(0+(MID(A1,5,1)&RIGHT(A1)))*(LEN(A1)=8)

 

[István Hirsch]

Maybe this...

=NOT(ISNUMBER(0+LEFT(A1,1)))*NOT(ISNUMBER(0+MID(A1,2,1)))*NOT(ISNUMBER(0+MID(A1,4,1)))*NOT(ISNUMBER(0+MID(A1,7,1)))*(MID(A1,3,1)&MID(A1,6,1)="--")*ISNUMBER(0+(MID(A1,5,1)&RIGHT(A1)))*(LEN(A1)=8)

Hi Rick,

When I loaded the formula, excel immediately asked for permission to modify the formula, and when I clicked yes, it worked quite correctly for the data provided.
I can not find what excel modified.

At me, the formula gives "1" for this: (A-t6-t6

 

[Rick Rothstein]

Hi Rick,

At me, the formula gives "1" for this: (A-t6-t6

Yeah, it turns out that NOT(ISNUMBER(... is not a sufficient test... back to the drawing boards (really annoying as this is soooo easy to do in VB).

 

[Rick Rothstein]

Yeah, it turns out that NOT(ISNUMBER(... is not a sufficient test... back to the drawing boards (really annoying as this is soooo easy to do in VB).

This formula should work correctly though (he says with fingers crossed)...

=(MAX(ABS(CODE(LEFT(UPPER(F1)))-77.5))<13)*(MAX(ABS(CODE(MID(UPPER(F1),2,1))-77.5))<13)*(MID(F1,3,1)="-")*(MAX(ABS(CODE(MID(UPPER(F1),4,1))-77.5))<13)*(MAX(ABS(CODE(MID(UPPER(F1),5,1))-52.5))<5)*(MID(F1,6,1)="-")*(MAX(ABS(CODE(MID(UPPER(F1),7,1))-77.5))<13)*(MAX(ABS(CODE(RIGHT(UPPER(F1)))-52.5))<5)*(LEN(F1)=8)

 

[taliapaw]

Sorry for my tardy reply. the formula will be used in data validation.

As for István's suggestion it did not work using 52,5 and 77,5 it gave me an error when I entered it into the data validation about not trying to type a formula.

 

[Rick Rothstein]

Sorry for my tardy reply. the formula will be used in data validation.

As for István's suggestion it did not work using 52,5 and 77,5 it gave me an error when I entered it into the data validation about not trying to type a formula.

Which formula are you referring to? What message number was it in?

 

[taliapaw]

Post #8 where István's modified your formula in post #4.

 

[Rick Rothstein]

Post #8 where István's modified your formula in post #4.

Did you see the formula I posted in Message #16 yet?