How to count the number of characters before a certain word appearing several times in a cell in Google Sheets?

The search function can take a third parameter which is where in the string you want to start the search, so if you find the first instance of "apple" and then run another search starting at that position plus 1, you will find the second instance.

first instance:

=search("apple", A2)

second instance:

=search("apple", A2, search("apple", A2) + 1)

subtract the second from the first:

=search("apple", A2, search("apple", A2) + 1) - search("apple", A2)

To just get the length between you would have to subtract the length of the word "apple" or whatever you are searching for.

If you want to do it with a script, you can play with the “indexOf” functions. It counts how many characters are from the beginning of the string until the word you introduce. indexOf works like this:

indexOf(string, [pos])

If you don’t specify any position it will start from the beginning, otherwise it will count from the position you introduced. With that being said, you can apply the following logic:

var string = sheet.getRange("your cell").getValues().toString();

var apple_length = "apple".length;  
var first_apple = string.indexOf("apple") + apple_length;
var second_apple = string.indexOf("apple", first_apple);

var result = second_apple - first_apple;

Since it counts until it finds the word, we need to add the word’s length to the formula, and use that result as the starting position for the second indexOf.

For more information, check https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/indexOf

EDIT

var appletree = [];
var apple_length = "apple".length;
var char_counter = 0;

while (true) {

var apple = your_string.indexOf("apple", char_counter) + apple_length;

appletree.push(apple);

char_counter = char_counter + apple;


if (char_counter >= your_string.length) 
 break;

};
};

This is actually simpler and better as it doesn't matter the string length or how many apples you put.

=INDEX(LEN(SPLIT($A$1,"apple",0)))

• SPLIT the text by apple
• Calculate LENgth of each string

• INDEX to get the respective index in the array

  =INDEX(LEN(SPLIT($A$1,"apple",0)),2)
  

var str = "I plucked an apple, from the red apple tree";
var len = str.split('apple', 2).map(e=>e.length);
console.info(len);
console.info(len[1]);

=FINDB("♥", SUBSTITUTE(A1, "apple", "♥"))-1