Limit Output of Array Formulas to 128 Characters in Google Sheets

google sheets

I have a Google Sheet where the users are building a name in a column based on content entered into previous columns. I need to limit the output of the formula to 128 characters (limitation of names in Vimeo).
I was wondering what is the best way to alert the user that the video name they are creating is longer than 128 characters and that they need to shorten it.
Is there any formula where the number of characters could be counted, and if the total characters were more than 128 characters there could be some way to let the user know how many characters they have exceeded 128 characters by?
That would be very useful to the person creating the name. I can't allow a name longer than 128 characters to be created.

The 3 different formulas I have used across 3 different sheets are:

formula 1

=ARRAYFORMULA(IF($E2:$E="Yes", $D2:$D, IF(ISBLANK($A2:$A), "", $A2:$A & " - ") & IF(ISBLANK($B2:$B), "", $B2:$B & " - ") & IF(ISBLANK($C2:$C), "", $C2:$C & ". ") & $D2:$D))

formula 2

=ARRAYFORMULA(IF(ISBLANK($A2:$A), "", $A2:$A & " - ") & IF(ISBLANK($B2:$B), "", $B2:$B & " - ") & IF(ISBLANK($C2:$C), "", $C2:$C & ". ") & $D2:$D)

formula 3

=ARRAYFORMULA(($A2:$A & " - ") & $B2:$B)

Here is a copy of my demo sheet
https://docs.google.com/spreadsheets/d/1AIdZmFqtojG_7OLY_twJhmFewVTyoFAsozzbeSeHun4/edit?usp=sharing

The formulas are in Row 2 of the tabs:

  • Formula 1: CC videos – Column I Row 2
  • Formula 2: AB videos – Column H Row 2
  • Formula 3: @home promos – Column F Row 2

I don't know how to apply LEFT to an array formula and I also don't know how to calculate remaining characters.

Best Answer

You want to highlight cell values that are longer than 128 characters, and also indicate the number of excess characters.

This answer addresses the scenario applying to sheet = "CC videos". You can adapt it to other sheets. The solution consists of two components:

  • a Helper column that displays the number of characters in the file name in excess of 128.
  • Conditional formatting that highlights only the cells that have a length greater than 128.

Helper Column

Create a helper column. This could be anywhere on a sheet, but it makes sense that it should be in the column to the immediate right of the file name.

Using sheet = "CC videos" as an example, insert this formula into cell J2:

={arrayformula( If(Len(I2:I)>128,len(I2:I)-128, iferror(1/0) ) )}

Conditional Formatting

  • Select "Conditional Formatting".
  • Apply to Range "I2:I"
  • Custom Formula
  • =len(I2)>128
  • Choose formatting style to suit.