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.

 

[Rick Rothstein]

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.

Your layout and possible data is not entirely clear from your description... if all the values in Column F are 6 characters long like this TTT#T# and you simply want to insert the dashes, then try this formula...

=LEFT(F1,2)&"-"&MID(F1,3,2)&"-"&RIGHT(F1,2)

 

[taliapaw]

My apologies some sample data would be TB-F4-T2 I would like to try and restrict to this format. I use T for text and # for numbers. Also the letters can be upper or lower case there is not restriction on that.

 

[Rick Rothstein]

My apologies some sample data would be TB-F4-T2 I would like to try and restrict to this format. I use T for text and # for numbers. Also the letters can be upper or lower case there is not restriction on that.

See if this works for you...

=AND(LEN(C1)=8,MAX(ABS(CODE(MID(UPPER(MID(C1,5,1)&RIGHT(C1)),ROW(1:2),1))-52.5))<5,MAX(ABS(CODE(MID(UPPER(LEFT(C1,2)&MID(C1,4,1)&MID(C1,7,1)),ROW(1:4),1))-77.5))<13,MID(C1,3,1)&MID(C1,6,1)="--")

 

[taliapaw]

It doesn't seem to work for me.

 

[Rick Rothstein]

It doesn't seem to work for me.

It might be helpful if you show us some of the values it says are TRUE that are actually not TRUE so that we can test specific values that do not work for you.

 

[taliapaw]

The formula you provided wouldn't accept any format I entered.

I've been playing around with this formula but it allows all the following examples as well.

=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)

correct rr-t6-t6

incorrect 1r-t6-t6

incorrect r1-t6-t6

incorrect rr-16-t6

incorrect rr-t6-16

incorrect 11-t6-t6

incorrect rr-11-t6

incorrect rr-t6-11

incorrect rr-11-11

incorrect r1-11-11

incorrect 1r-11-11

<colgroup><col></colgroup><tbody>
</tbody>

completely rejects these formats 11-11-11 and rr-rr-rr

 

[István Hirsch]

Does the formula in post #4 work for you if you use 52,5 and 77,5 instead of 52.5 and 77.5?

If not, please show us some data in one column along with the expected result in another.

 

[Rick Rothstein]

The formula you provided wouldn't accept any format I entered.

I see the problem but before I attempt a fix, will the formula be using in Data Validation or simply on a cell on the worksheet?

Also, in case you missed it, please answer István's question in Message #8 as well.

 

[István Hirsch]

If this is a validation, test this formula:

UNDER RECONSTRUCTION