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屋!