How to count the number of characters between each

2019-08-20 11:21发布

问题:

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!