Split string for every X number of characters in a Google Sheet-CodePudding

I want to split a cell value into multiple lines by splitting a string by every 5th character.

Example: in below sample (screenshot) we have ABCDEFGHIJKLMNOPQRSTUVWXYZ in A1 and in B1 I want the string to be split after every 5th letter.

How can I do this?

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)")))
```

References:

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