Google工作表JOIN的文本结果长于50000个字符的限制 [英] Google sheets Text result of JOIN is longer than the limit of 50000 characters
问题描述
= transpose(split(join(,arrayformula(rept(filter(A1:A,len(A1:A))& char ),counta(B1:B)))),char(9999)))
:
= transpose(split(rept(join(char(9999),filter(B1:B,len(B1:B ))& char(9999),counta(A1:A)),char(9999)))
但是当我在列表中使用它时,我分别得到C1和D1中的错误;
错误
< blockquote>
JOIN的文本结果长于50000个字符的限制。
REPT的文本结果长于32000个字符的限制
我用一个较小的列表测试了它;
abc 1 2
并设法让我的列表生成这个结合2格
a 1
a 2
a 3
b 1
b 2
b 3
但是我合并的列表中有更多的文字列
关于如何组合我的列表的任何建议如上所示,但在列A中有132种可能性,在列b中有52种可能性。每行在每行中有70到150个字符的文本。
任何帮助将不胜感激。
转到菜单工具→脚本编辑器...
粘贴此代码:
函数crossJoin(arr1,arr2,delim){
delim = delim || ;
var result = [];
var row = []; (var i = 0; j row = [] ;
row.push(''+ arr1 [0,i] + delim + arr2 [0,j]);
result.push(row);
}
}
返回结果;
保存项目。
在电子表格中使用它作为常规函数:
= crossJoin(A1:A132,B1:B52)
Optionaly使用分隔符:
$ b = crossJoin(A1:A132,B1:B52, - )
So I am trying to combine text from column A and match it with each possibility of column B.I used the formulas
In column C1:
=transpose(split(join("", arrayformula(rept(filter(A1:A, len(A1:A))&char(9999), counta(B1:B)))), char(9999)))
In column D1:
=transpose(split(rept(join(char(9999), filter(B1:B, len(B1:B)))&char(9999), counta(A1:A)), char(9999)))
but when I use it in my list I get the error in C1 and D1 respectively;
Error
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 2 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
Any suggestions on how to combine my lists as shown above but with 132 possibilities in column A and 52 possibilities in column b. Each line has between 70 and 150 characters of text in each row.
Any help would be appreciated.
Go to menu Tools → Script Editor...
Paste this code:
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;
}
Save project.
Use it as regular function in spreadsheet:
=crossJoin(A1:A132,B1:B52)
Optionaly use delimeter:
=crossJoin(A1:A132,B1:B52, "-")
这篇关于Google工作表JOIN的文本结果长于50000个字符的限制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!