I'm extending and clarifying my previous question asked Here:
How to return the number of characters between each 2 occurrences of a word in a cell?
And across 2 or more cells in a range/ 1 column of cells?
For example:
Please see Sheet6 here
Cell A2 contains 4 times the word "apple", each time separated by x number of characters.
How to return those intermediary numbers as shown in Sheet6 columns B, C, D, and E?
How to do that with a script? Ideally for the range of A2:A to set the relative characters counts per corresponding cells in ranges B2:B, C2:C, D2:D, E2:E, and F2:F?
Taking this string as example:
In A2:
0 apple one apple 2 apple three apple four.
The returned needed results would be set as:
In B2: 2 (for 2 the characters of the string "0(-)")
In C2: 5 (for 5 the characters of the string "(-)one(-)")
In D2: 3 (for 3 the characters of the string "(-)2(-)")
In E2: 7 (for 7 the characters of the string "(-)three(-)")
In F2: 6 (for 6 the characters of the string "(-)four.", with the "." appearing as last character in the cell as taken criterion to close the count.)
And this other example:
to take into account a string across 2 cells A3 and A4:
A3= zero apple one apple 2
A4= apple three apple four.
In B3: 5 (for the 5 characters of the string "zero(-)")
In C3: 5 (for the 5 characters of the string "(-)one(-)")
In D4: 3 (for 3 the characters of the string "(-)2(-)". In D4 because the next "apple" is one row below, the count is set relatively one row below as well).
In E4: 7 (for 7 the characters of the string "(-)three(-)")
In F4: 6 (for 6 the characters of the string "(-)four.", with the "." appearing as last character in the cell taken as criterion to close the count.)
So far I've adapted @Jescanellas previous script to the following here
function numcharsbetweenocc1and2ofApple() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var your_string = sheet.getRange("B2").getValues().toString();
var appletree = [];
var apple_length = "apple".length;
var char_counter = 0;
while ("B2" !== "") {
var apple = your_string.indexOf("apple", char_counter) + apple_length;
appletree.push(apple);
char_counter = char_counter + apple;
var result = apple;
sheet.getRange("D2").setValue(result);
if (char_counter >= your_string.length)
break;
};
};
But that returns the number of character up to the last occurrence of "apple". And only for one current cell, it doesn't work for the whole column when I change the .getRange("B2")
to .getRange("B2:B")
and sheet.getRange("D2:D").setValue(result);
to sheet.getRange("D2:D").setValue(result);
.
Sheet4
Here again the expected result:
Sheet6 here
Thanks for your input and help!