How do I use the split function in Google Sheets correctly?

Question

I want to get data from the text found in 2.. In 1. Is the data I have available and in 3. You will find the wanted result.

1. I have the following information available

Categories
Kleur
Soorthek
Bevestigingswijze

2. I am getting this text from scraping: BevestigingswijzeKlembevestigingKleurWitSoorthekSpijlenhek

3. I want this as a result by using a function in Google Sheets.

Wanted Result
KleurWit
SoorthekSpijlenhek
BevestigingswijzeKlembevestiging

Thank you in advance!

Answer 1

You could accomplish this with an Apps Script custom function. To achieve this, follow these steps:

• In your spreadsheet, select Tools > Script editor to open a script bound to your file.
• Copy this function in the script editor, and save the project:

function splitScrape(categories, scrape) {
  const indexes = categories.map(c => scrape.indexOf(c[0])).sort();
  const split = indexes.map((index, j) => scrape.slice(index, indexes[j+1]));
  return split;
}

The sample above won't detect multiple occurrences of the same category in the scrape string, and it will only handle the first one (if a second parameter is not provided, indexOf only detects the first occurrence). In order to detect multiple occurrences, I'd suggest replacing the function with this one:

function splitScrape(categories, scrape) {
  const indexes = [];
  categories.flat().filter(String).forEach(c => {
    let index = scrape.indexOf(c);
    while (index > -1) {
      indexes.push(index);
      index = scrape.indexOf(c, index + 1);
    }
  });
  indexes.sort((a, b) => a-b);
  const split = indexes.map((index, j) => scrape.slice(index, indexes[j+1])).filter(String);
  return split;
}

• Now, if you go back to your spreadsheet, you can use this function like any in-built one. You just have to provide the appropriate ranges where the Categories and the scrape string are located, as you can see here:

Reference:

• Custom Functions in Google Sheets