9

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
    Not while entering. Upon leaving it is possible. – Jacob Jan Tuinstra Apr 19 '15 at 18:58
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.

| improve this answer | |
6

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

| improve this answer | |
  • How to combine this with Text Not Containing "XYZ, ABC" ? – shadowz1337 Feb 18 '19 at 7:39
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

| improve this answer | |
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:

| improve this answer | |
  • 1
    Basically is the same as this other answer but with a convuluted formula. – Rubén Nov 22 '16 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. – GrapesInASalad Dec 4 '16 at 16:01

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.