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.

 

[taliapaw]

I've been working with it. I'm just really new to excel and I'm trying to figure out how to enter it because data validation won't allow the formula because of it's length.

 

[taliapaw]

I believe I manged to work around the length issue but when I enter OB-F1-S1 is won't allow my entry.

 

[István Hirsch]

I believe I manged to work around the length issue but when I enter OB-F1-S1 is won't allow my entry.

As I mentioned earlier, my previous suggestions do not work.

In the meantime I have worked out a new formula (partly tested, it seems to work with OB-F1-S1 and others):


=IFERROR(IF((LEN(A2)=8)*SUMPRODUCT((ABS(77.5-CODE(UPPER(MID(A2,ROW($1:$8),1))))<13)*MID("99090090",ROW($1:$8),1)+(ABS(52-CODE(MID(A2,ROW($1:$8),1)))<=5)*MID("00009009",ROW($1:$8),1)+(MID(A2,3,1)="-")+(MID(A2,6,1)="-"))=70,"correct","incorrect"),"incorrect")

 

[Rick Rothstein]

I've been working with it. I'm just really new to excel and I'm trying to figure out how to enter it because data validation won't allow the formula because of it's length.

Stop trying... the formula is too long for Data Validation (I developed it in a cell and forgot about the length limit on Data Validation formulas). I have managed to shorten the formula, but not enough to satisfy the length limit. I could make the formula short enough if the text in your cells were all of the same case (all upper or all lower case), but with the text being above to be either upper or lower case, I cannot find a way to shorten the formula enough for use in Data Validation. Another possibility is to use VBA event code to monitor the cells and react with a MessageBox if the pattern is incorrect. If you are willing to go that route, you need to tell me the address range of all the cell that would need to be monitored.

 

[Rick Rothstein]

As I mentioned earlier, my previous suggestions do not work.

In the meantime I have worked out a new formula (partly tested, it seems to work with OB-F1-S1 and others):


=IFERROR(IF((LEN(A2)=8)*SUMPRODUCT((ABS(77.5-CODE(UPPER(MID(A2,ROW($1:$8),1))))<13)*MID("99090090",ROW($1:$8),1)+(ABS(52-CODE(MID(A2,ROW($1:$8),1)))<=5)*MID("00009009",ROW($1:$8),1)+(MID(A2,3,1)="-")+(MID(A2,6,1)="-"))=70,"correct","incorrect"),"incorrect")

Lightly tested also, but yes, that formula seems to work; however, the OP has said the formula will be used in Data Validation (Message #17), so this modification of your above formula is what the OP would need for that...

=(LEN(A2)=8)*SUMPRODUCT((ABS(77.5-CODE(UPPER(MID(A2,ROW($1:$8),1))))<13)*MID("99090090",ROW($1:$8),1)+(ABS(52-CODE(MID(A2,ROW($1:$8),1)))<=5)*MID("00009009",ROW($1:$8),1)+(MID(A2,3,1)="-")+(MID(A2,6,1)="-"))=70

By the way... nice job coming up with that formula!

 

[taliapaw]

The formula in post #25 works like a charm for all the values I've tested so far. Thank you both for your help. Is there any site or book you recommend that I can help me understand data validation formulas better?

 

[István Hirsch]

Thanks, Rick.

I for one do not know much about data validation, but Rick knows more.

 

[Rick Rothstein]

I for one do not know much about data validation, but Rick knows more.

Unfortunately, that is not true. I'm guessing I know about as much about Data Validation as you do... I pretty much stumble into the eventual formula.

 

[taliapaw]

So good old fashioned trial and error with help from lovely people like yourselves on forums. Thanks again I will keep on working on other columns hopefully they prove to be easier then this one was.