Forum Discussion

Sam1209's avatar
Sam1209
Brass Contributor
Aug 21, 2019
Solved

Column Validation - SharePoint Online List

I have a Single line of text field in a SharePoint Online list where the data needs to be entered in the following format:

NLLLNNNNN

(N = Number and L = Letter)

Is anyone able to help with the formula please?

Lists
SharePoint Online
  • WinfredLiu's avatar
    WinfredLiu
    Aug 23, 2019

    Hi Sam1209 

     

    This will do:

     

    =AND(
      LEN([Column Name])=9,
      ISNUMBER(VALUE(MID([Column Name],1,1))),
      NOT(ISNUMBER(VALUE(MID([Column Name],2,3)))),
      ISNUMBER(VALUE(MID([Column Name],5,5))),
      AND( MID([Column Name],2,1)>="A", MID([Column Name],2,1)<="Z" ),
      AND( MID([Column Name],3,1)>="A", MID([Column Name],3,1)<="Z" ),
      AND( MID([Column Name],4,1)>="A", MID([Column Name],4,1)<="Z" )
    )

     

     

  • WinfredLiu's avatar
    WinfredLiu
    Brass Contributor
    Aug 23, 2019

    Hi Sam1209 

     

    This will do:

     

    =AND(
      LEN([Column Name])=9,
      ISNUMBER(VALUE(MID([Column Name],1,1))),
      NOT(ISNUMBER(VALUE(MID([Column Name],2,3)))),
      ISNUMBER(VALUE(MID([Column Name],5,5))),
      AND( MID([Column Name],2,1)>="A", MID([Column Name],2,1)<="Z" ),
      AND( MID([Column Name],3,1)>="A", MID([Column Name],3,1)<="Z" ),
      AND( MID([Column Name],4,1)>="A", MID([Column Name],4,1)<="Z" )
    )

     

     

Resources