CODEWITHSUNDEEP
google-sheetsgoogle-docs
Khaled Al Hage Ismail
Khaled Al Hage Ismail

Reputation: 1939

Google spreadsheet text length validation

I am working on Google spreadsheet. I am trying to make a validation rule on the text length. For example the text length must be equal to 12 characters which includes number, on dash and one capital character. Example: 123-56C89112

I tried to search in google groups but no result! can you help in this please

Upvotes: 0

Views: 2355

Answers (1)

oliver13
oliver13

Reputation: 1036

You can use Data > Validation > Custom Formula is

=REGEXMATCH(B3, "^(?=.{12}$)(?=(^[^A-Z]*[A-Z]{1}[^A-Z]*$))(?=(^[^-]*[-]{1}[^-]*$)).*")

I'm not an expert on Regex so this may be a bit over the top, but it appears to work:

^(?=.{12}$)(?=(^[^A-Z]*[A-Z]{1}[^A-Z]*$))(?=(^[^-]*[-]{1}[^-]*$)).*

Regular expression visualization

Debuggex Demo

This allows any string with exactly 12 characters, 1 upper case letter and 1 dash.

EDIT: The (Lookahead) Regex doesn't seem to work in Spreadsheets (see comment)

EDIT2: Using a custom function like the following seems to work, but only if you use warnings (instead of rejections) for some reason (bug?) 

function test(myString) { 
return myString.match("^(?=.{12}$)(?=(^[^A-Z]*[A-Z]{1}[^A-Z]*$))(?=(^[^-]*[-]{1}[^-]*$)).*")!=null;
 }

Upvotes: 1

Related Questions