JOIN的结果超过了50,000个字符的限制 [英] Result of JOIN is longer than the limit of 50,000 characters

问题描述

我正在尝试合并A列中的文本，并将其与B列的每种可能性进行匹配.我使用了以下公式:

I am trying to combine text from column A and match it with each possibility of column B.I used the formulas:

在C1中:

=transpose(split(join("", arrayformula(rept(filter(A1:A, len(A1:A))&char(9999), counta(B1:B)))), char(9999)))  

在D1中:

=transpose(split(rept(join(char(9999), filter(B1:B, len(B1:B)))&char(9999), counta(A1:A)), char(9999)))

但是当我在列表中使用它时，分别在C1和D1中出现这些错误；

but when I use it in my list I get these errors in C1 and D1 respectively;

JOIN的文本结果超出了50000个字符的限制
REPT的文字结果超出了32000个字符的限制

Text result of JOIN is longer than the limit of 50000 characters
Text result of REPT is longer than the limit of 32000 characters

我用一个较小的清单进行了测试:

I tested this out with a smaller list of just:

a b c 1 2 

并设法将两个单元格合并后生成我的列表:

and managed to get my list to generate this after combining the two cells:

a 1
a 2
a 3
b 1
b 2
b 3

但是我要合并的列表的每一列中都有很多文本.

but the list I am combining has a lot more text in each of the columns.

关于如何合并我的列表的任何建议，如上所示，但在A列中有132种可能性，在B列中有52种可能性?

Any suggestions on how to combine my lists as shown above but with 132 possibilities in column A and 52 possibilities in column B?

每行每行中的文本介于70到150个字符之间.

Each line has between 70 and 150 characters of text in each row.

推荐答案

转到菜单Tools → Script Editor...

粘贴此代码:

function crossJoin(arr1, arr2, delim) {

  delim = delim || '';

  var result = [];
  var row = [];
  for (var i = 0; i < arr1.length; i++) {
    for (var j = 0; j < arr2.length; j++) {
      row = [];
      row.push('' + arr1[0,i] + delim + arr2[0,j]);
      result.push(row);
    }   
  }
  return result;
}

保存项目.

将其用作电子表格中的常规功能:

Use it as regular function in spreadsheet:

=crossJoin(A1:A132,B1:B52)

可选地使用定界符:

=crossJoin(A1:A132,B1:B52, "-")

这篇关于JOIN的结果超过了50,000个字符的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！