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 for...
Lists
SharePoint Online
  • WinfredLiu's avatar
    WinfredLiu
    Aug 22, 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 22, 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" )
)