SOLVED

Column Validation - SharePoint Online List

%3CLINGO-SUB%20id%3D%22lingo-sub-814561%22%20slang%3D%22en-US%22%3EColumn%20Validation%20-%20SharePoint%20Online%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814561%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Single%20line%20of%20text%20field%20in%20a%20SharePoint%20Online%20list%20where%20the%20data%20needs%20to%20be%20entered%20in%20the%20following%20format%3A%3C%2FP%3E%3CP%3ENLLLNNNNN%3C%2FP%3E%3CP%3E(N%20%3D%20Number%20and%20L%20%3D%20Letter)%3C%2FP%3E%3CP%3EIs%20anyone%20able%20to%20help%20with%20the%20formula%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-814561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819407%22%20slang%3D%22en-US%22%3ERe%3A%20Column%20Validation%20-%20SharePoint%20Online%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819407%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F359684%22%20target%3D%22_blank%22%3E%40Sam1209%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAND(%3CBR%20%2F%3E%26nbsp%3B%20LEN(%5BColumn%20Name%5D)%3D9%2C%3CBR%20%2F%3E%26nbsp%3B%20ISNUMBER(VALUE(MID(%5BColumn%20Name%5D%2C1%2C1)))%2C%3CBR%20%2F%3E%26nbsp%3B%20NOT(ISNUMBER(VALUE(MID(%5BColumn%20Name%5D%2C2%2C3))))%2C%3CBR%20%2F%3E%26nbsp%3B%20ISNUMBER(VALUE(MID(%5BColumn%20Name%5D%2C5%2C5)))%2C%3CBR%20%2F%3E%26nbsp%3B%20AND(%20MID(%5BColumn%20Name%5D%2C2%2C1)%26gt%3B%3D%22A%22%2C%20MID(%5BColumn%20Name%5D%2C2%2C1)%26lt%3B%3D%22Z%22%20)%2C%3CBR%20%2F%3E%26nbsp%3B%20AND(%20MID(%5BColumn%20Name%5D%2C3%2C1)%26gt%3B%3D%22A%22%2C%20MID(%5BColumn%20Name%5D%2C3%2C1)%26lt%3B%3D%22Z%22%20)%2C%3CBR%20%2F%3E%26nbsp%3B%20AND(%20MID(%5BColumn%20Name%5D%2C4%2C1)%26gt%3B%3D%22A%22%2C%20MID(%5BColumn%20Name%5D%2C4%2C1)%26lt%3B%3D%22Z%22%20)%3CBR%20%2F%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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?

1 Reply
Highlighted
Best Response confirmed by Sam1209 (Occasional Contributor)
Solution

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" )
)