How to limit the length of data in a cell in Google Sheets?

Question

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?

Answer 1

Use a Data Validation with a custom formula.

If the cell to limit the value length is A1 and the length limit is 5, then the custom formula is

=LEN(A1)<=5

References

• Data Validation - Docs Editors Help

Answer 2

The following little script limits the cell input to 5 positions and adds the surplus as a note to the cell.

Code

function onEdit(e) {
  var limit = 5;
  if(e.value.length > limit) {
    e.range.setValue(e.value.substring(0, limit) + "...");
    e.range.setNote("Remaining text was: \n....." + e.value.substring(limit));
  }
}

Explained

On each and every edit, the onEdit(e) trigger is fired and every time it will check whether the cells value (e.value) has a length larger than 5. If so, the active cell will receive a new value of no more than 5 positions (setValue & substring(0,5)). The remainder of the text is added as a note.

Screenshot

Example

I've created an example file for you: Limit cell input
Add the code under Tools > Script editor from the spreadsheet menu. In the script editor, paste the code and press the save button.

Answer 3

The following formula limits the cell input to 10 positions.

If the cell to limit the value length is A2 and the length limit is 10, then the custom formula is

=LEFT(A2,10)

Example:

Result:

Done. But if you wanna add an ellipsis after the result you can edit this following:

=CONCATENATE(""&LEFT(A2,10)&" ...")

Result:

Answer 4

We can also use Data Validation with a custom formula to limit the length of a cell by its word count (aka, to set a maximum number of words in a cell).

If A1 is the cell we are limiting by word count, and 10 is the maximum number of words, then the formula is:

=COUNTA(SPLIT(A1, " "))‹=10

References:

• Data Validation - Google Docs Help

Answer 5

Here is a simpler answer, goto Format > Text wrapping > Clip

This will auto-clip the text in that cell. You can select rows / columns of cells and apply the same formatting style.

No codes required!

There is also an option of "wrap" ... this puts all the overflowing text in paragraph form. But the only disadvantage to this is that the cells beside them have huge empty chunks and it might not be pleasing to look at for many..

Attaching screenshots for reference.

Hope this was helpful.