12

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?

1
  • 2
    Not while entering. Upon leaving it is possible. Apr 19, 2015 at 18:58

5 Answers 5

9

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

3
  • How to combine this with Text Not Containing "XYZ, ABC" ? Feb 18, 2019 at 7:39
  • How does this work for a range, like a whole column?
    – snh_nl
    Feb 23, 2021 at 12:20
  • @snh_nl Please post a new question.
    – Rubén
    Feb 23, 2021 at 16:05
6

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

enter image description here

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.

1

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:

example 1 formula

Result:

example 1 result

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

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

Result:

example 2 result

0

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:

2
  • 1
    Basically is the same as this other answer but with a convuluted formula.
    – Rubén
    Nov 22, 2016 at 23:52
  • In fact that answer is to limit strings by number of characters, the formula I shared limits by words. But yes, much more convoluted. Dec 4, 2016 at 16:01
0

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.

Steps

preview

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.