Formula to check if a column has one of two values and LEN=9

Question

I'm trying to work out a formula to check if a column has CIP or GEN as the first three characters, followed by 7 numerals. I have a formula to check for CA1234567.

=(LEN([Order Number])=9)+(LEFT([Order Number],2)="CA")=2

Although this does not check if there are 7 digits, just that the field is 9 characters in length.

Answer 1

Of the top of my head:

 =AND( 
       OR( 
            LEFT( x , 3 ) = "CIP"
          , LEFT( x , 3 ) = "GEN"
         )
      , ISNUMBER( RIGHT( x , 7 ) ) 
      , LEN( x ) = 10
     )

All functions that actually work in Calculated Columns:

http://www.viewmaster365.com/functions

tip:

• use Excel, name cell A1 the name of your SharePoint Field x
  (use B1, C1,.. for more named references)
• make your Formula work in cell A2
• copy/paste in SharePoint

Answer 2

You should be able to combine the ISNUMBER, LEFT, and RIGHT functions to check each number and AND the results. Syntax here.