Search code examples
excelvalidationexcel-formula

Custom Excel formula for length restriction of substring within a string in a cell

I have a use case where I want to put in the following data validation in a cell:

  1. Restrict the overall length of text in the cell
  2. Restrict the number of words a user can enter. I am doing this by counting the number of "," and putting in a restriction on that
  3. For each word separated by comma in that cell, the length should not be more than N. Ex: If N is 6 the apple,banana is valid since length of apple and banana is <= 6 but apple,avacado is invalid since length of avacado > 6

I am currently using the following:

=AND(LEN(A1)<=100, LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))<=10)

I am not sure how I can I add the length restriction for individual substring. I hope I have added all the details.

Solution 
  • =AND(AND(LEN(TEXTSPLIT(A1,","))<=6),LEN(A1<=100),COUNTA(TEXTSPLIT(A1,","))<=10)

    String length 100 or less symbols. Less or equal to 10 words (separated by comma). Every separate word is 6 or less symbols. Spaces are included in every case.