1

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.

6
  • Yes, you can do that. Seems though your scenario is quite complex. Please read how to and share a test sheet so as you can be easier helped. May 14, 2020 at 14:33
  • 1
    In a comment to my answer to a previous question I already suggested you to use LEFT but you didn't mention that you tried to use this function. Could you please try to use this function? May 14, 2020 at 14:54
  • Thanks Rubén. I was not sure how to apply LEFT. I will share a test sheet.
    – user246475
    May 14, 2020 at 15:01
  • As @Rubén mentioned. Have you tried his suggestion? Did it work for you? If yes, why the new post. If no, please edit your question and add what you tried so far. As mentioned you seem to have complex formulas and without a demo spreadsheet, you leave users who try to help guessing. May 14, 2020 at 15:04
  • 1
    Looking at your sheet. Sorry. I'm lost. It is full of formulas, columns, tabs, etc. As mentioned, "Include only but all necessary demo data.... Not too little, not too much. Do not overload the sheet. No need to have 500 rows and 50 columns. As well as not just 2 rows and 1 column. Just as much as needed". Also, "Give an example of your expected results." May 14, 2020 at 20:43

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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