Shorter or better write CF formula

After much toil, I have a working Conditional Formatting formula. This formula takes only the end (anything after the 2nd dash) of a numeric/text string, and evaluates it for the following:

• Is the end portion after the 2nd dash ("-") a number?
• If criteria cell = 5, is the end number exactly 6 digits long?
• If the criteria cell <> 5, is the end number exactly 5 digits long?

My first formula is in column E of the attachment - but I realized, at least I think, I could safely remove the SUBSTITUTE function, so I have it down to the formula in column F. The formula I currently have is:

Formula:

Please Login or Register  to view this content.

My question is, is there either a way to shorten this formula - or - re-write it with the same results?

The attachment list the expected result and if FALSE, the reason why.

Sample 5.xlsx




...FYI, I have another working formula which evaluates the first and middle parts of the string, including the dashes. For the curious, I put that in column J, and the formula is:

Formula:

Please Login or Register  to view this content.

...I can use the 2 formulas separately but would like to combine if possible; however, as written, combining goes over the 255 limit by < 20 characters. Also, FYI, VBA cannot be used for this project.

formula1
=AND(ISNUMBER(--MID(B2,FIND("-",B2,4)+1,21)),LEN(MID(B2,FIND("-",B2,4)+1,21))=6-A2<>5)

@tim201110 The first portion works great and shortens the existing formula by 11 characters! Question, why you choose "21" as a number of characters for the MID statement? Arbitrary? Also, the end portion, doesn't work out ... not sure if you understood the Criteria Number is just an indicator for determining if the length should be 6 or 5 digits - that actual value in the criteria number field is meaningless. But thanks!

21 because i was lucky in Caesars.
You can use any greater than 6
this way is better
=AND(ISNUMBER(--MID(B2,FIND("-",B2,4)+1,21)),LEN(MID(B2,FIND("-",B2,4)+1,21))=6-(A2<>5))