I want to split a cell value in to multiple lines.
Example: in below sample we have A to Z
in A1
and in B1
I need words to be splitted after every 5th word.
CodePudding user response:
function test() {
let s = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
let a = s.split("").slice();
let d = 0;
s.split("").forEach((r, i) => {
if (i > 0 && i % 5 == 0) {
a.splice(i d , 0, '\n');
}
});
Logger.log(a.filter(e => e).flat().join(""));
}
Execution log
8:20:25 PM Notice Execution started
8:20:24 PM Info ABCDE
FGHIJ
KLMNO
PQRST
UVWXY
Z
8:20:26 PM Notice Execution completed
CodePudding user response:
From your google-apps-script
tag, when Google Apps Script is used, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. And, please put a custom function of =SAMPLE(A1)
to a cell. By this, the result value is put to the cell.
function SAMPLE(value) {
const v = [...value];
const res = [];
while (v.length > 0) {
res.push(v.splice(0, 5).join(""));
}
return res.join("\n");
}
Or, for example, if you want to use the custom function as =SAMPLE(A1)
and =SAMPLE(A1:D5)
, how about the following sample script?
function output_(value) {
const v = [...value];
const res = [];
while (v.length > 0) {
res.push(v.splice(0, 5).join(""));
}
return res.join("\n");
}
function SAMPLE(values) {
return Array.isArray(values) ? values.map(r => r.map(c => output_(c))) : output_(values);
}
Note:
For example, when you use a built-in formula, how about the following formula? This formula supposes the text of
ABCDEFGHIJKLMNOPQRSTUVWXYZ
to a cell "A1". Please put this formula in the cell.=JOIN(CHAR(10),REGEXEXTRACT(A1,REGEXREPLACE(A1,"(.{0,5})","($1)")))