How to SPLIT cell content into sets of 50000 characters in new columns Google Sheets

0
votes

I have 3 columns A, B & C as shown in the image. Column A contains the search key. The second column B contains names and their respective content in the third column C.

I am filtering rows that contain the text in A1 in B:C and concatenating them. The challenge is that each text in the third column is roughly 40k characters. The filter formula works well so the issue is the character limit. This formula =ArrayFormula(query(C1:C,,100000)) which I have in F1 concatenates more than 50000 characters but I am not how to apply it for my case.

Tried to wrap my formula in E1 inside the query function but it wasn't successful. Like so: =ArrayFormula(query(CLEAN(CONCATENATE(FILTER(C1:C, B1:B=A1))),,100000)).

I also tried to SPLIT the concatenated result into sets of 50000 characters and put the extras in the next columns but wouldn't manage either. The formula I tried in this case is: =SPLIT(REGEXREPLACE(CLEAN(CONCATENATE(FILTER(C1:C, B1:B=A1))),".{50000}", "$0,"),",")

enter image description here

The link to the spreadsheet https://docs.google.com/spreadsheets/d/1rhVSQJBGaPQu6y2WbqkO2_UqzyfCc3_76t4AK3PdF7M/edit?usp=sharing

1
google-sheets
CONCATENATE wasn't working with Arrayformula so after removing it, the following formula worked. =ArrayFormula(query(FILTER(C1:C, B1:B=A1),,100000)). Didn't have to SPLIT into new columns. Thanks. – Danstan Ongubo

1 Answers

1
votes

Since cell is limited to 50,000 characters, using CONCATENATE is not possible. Alternative solution is to use Google Apps Script's custom function. The good thing about Apps Script is it can handle millions of string characters.

To create custom function:

  1. Create or open a spreadsheet in Google Sheets.
  2. Select the menu item Tools > Script editor.
  3. Delete any code in the script editor and copy and paste the code below.
  4. At the top, click Save.

To use custom function:

  1. Click the cell where you want to use the function.
  2. Type an equals sign (=) followed by the function name and any input value — for example, =myFunction(A1) — and press Enter.
  3. The cell will momentarily display Loading..., then return the result.

Code:

function myFunction(text) {
  var arr = text.flat();
  var newStr = arr.join(' ');
  var slicedStr = stringChop(newStr, 50000);
  return [slicedStr];
}

function stringChop(str, size){
  if (str == null) return [];
    str = String(str);
    size = ~~size;
  return size > 0 ? str.match(new RegExp('.{1,' + size + '}', 'g')) : [str];
}

Example:

Based on your sample spreadsheet, there are 4 rows that matches the criteria of the filter and each cell contains 38,976 characters, which is 155,904 characters in total. Dividing it by 50,000 is 3.12. The ceiling of 3.12 is 4 which means we have 4 columns of data.

Usage:

Paste this in cell E1:

=myFunction(FILTER(C1:C, B1:B=A1))

Output:

enter image description here

Reference: