0

I'm trying to repeat a set comma separated values twice, leading this set to exceed the limit of 32000 characters that REPT allows.

Is there any way to use an alternative for this such as javascript or using the query functionality?

I've already tried building a Google script function replicating this but that threw an internal custom javascript error that could not be resolved.

This is the function: =REPT(JOIN(",",ARRAYFORMULA(REPT(SPLIT("1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,1323,1330,1331,1342,1345,1350,1355,1360,1380,1390",",")&",",19))),40)

This is the error:

Text result of REPT is longer than the limit of 32000 characters.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Moritz
  • 3
  • 3

3 Answers3

2

Maybe a little late for this answer, but if anybody else needs it, you can do a recurrency of player0's answer, and by combining it with a simple google script, create dynamic, parametrizable and large =REPT().

Taking your initial string, let's name it text1

    text1 = "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,1323,1330,1331,1342,1345,1350,1355,1360,1380,1390"

Calculate it's length. A simple way would be doing =LEN(text1). In this case, the length would be 199 characters. The =REPT() character limit is 32000, so for text1 the maximum amount of times you can repeat it is 32000/199 = 160.804. We will hold this value rounded down as:

    max_repts = ROUNDDOWN(32000/199) = 160

Now, having this, let's define the total amount of repetitions you need to do as XLargeNumber. With this and the max_repts, we can see the total of times we'll need to repeat the =REPT() statement by doing:

    amount_repts = ROUNDUP(XLargeNumber / max_repts)

The ROUNDUP here gives us the total amount of repts, but we still need to take into account that the last one will not probably be with the max_repts, but it will be less, so we also need:

    last_rept_amount = XLargeNumber - (max_repts*(amount_repts-1))

With this, we are going to dynamically generate the formula by making use of the {} array constructor from google sheets, and appending the =REPT() one aside each other, as well as using the ARRAYFORMULA(QUERY(range,,large_number)) technique than we can find here like this:

"=ARRAYFORMULA(QUERY("{"&REPT("REPT(text1,max_repts)"&IF(amount_repts-1>1,",",""),amount_repts-1)&IF(amount_reps-1>0,IF(amount_reps-1=1,",","REPT(text1, last_rept_amount)"),"")&"}"),,(LEN(text1)*amount_repts)))"

This will give us the formula as a text, so to pass it you can either copy and paste values, or instead, use a custom google script to introduce it in the cell you'd like. The script could look like this:

function set_formula() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Yourspreadsheet');
  var target = ss.getSheetByName('TargetSpreadsheet');
  var data = sheet.getDataRange().getValues();
  // 0 index row and column where the text formula is
  var formula = data[row][column];
  
  // Set formula
  target.getRange(start_row, start_col, num_rows, num_cols).setFormula([[formula]]);
  
}

Having this, you can generate a dynamic formula, and introduce it as needed. You can even automate it more by adding triggers, and attaching to moving data.

Sebastian Olivos
  • 41
  • 4
0

first, make sure you really know that you need it

your formula is too big but if you change 40 to 8 it will work:

=ARRAYFORMULA(REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8))

so now you just need to stack it in VR array 5 times:

=ARRAYFORMULA(QUERY({REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8);
 REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8);
 REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8);
 REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8);
 REPT(JOIN(",", REPT(SPLIT(
 "1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,
  1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,
  1323,1330,1331,1342,1345,1350,1355,1360,1380,1390", ",")&",", 19)), 8)},,999^99))

0

player0
  • 124,011
  • 12
  • 67
  • 124
0

SPLIT converts the text into numbers and REPT works only on text.

So consider reversing the sequence;

  • Repeat the string
  • Then split it

=split(rept("1010,1020,1050,1051,1052,1060,1070,1080,1081,1090,1091,1092,1093,1094,1100,1120,1150,1180,1200,1240,1250,1260,1280,1290,1300,1301,1310,1312,1320,1321,1323,1330,1331,1342,1345,1350,1355,1360,1380,1390,",2),",",true,true)

Tedinoz
  • 5,911
  • 3
  • 25
  • 35