How to count the number of characters before a cer

问题:

I've found the below formula to count the number of characters preceeding a certain word, following the solution provided here:

Count number of characters before a certain word in cell

=SEARCH("apple",A2)-1

My issue is the following:

How to count the intermediary characters between 2 occurrences of the same word in a single cell?

Adapting Nicole's example found here):

"I need to count the number of characters in a text string before a word. For example, lets say the word is "Apple". and the text string is "I plucked an apple from that beautiful tree". In this case the number of characters is 13."

Let say my example is:

"I plucked an apple, from the red apple tree".

The =SEARCH("apple",A2)-1 formula returns the number of characters up to the 1st occurence of the word "apple". Which would be 13.

How to count the number of characters inbetween the 2 occurences of the word "apple" with a script?

Which would be the length of ", from the red"?

Or is it possible to do it by adapting the formula =SEARCH("apple",A2)-1?

Thanks so much for your help and guidance!

回答1:

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.

回答2:

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.

回答3:

=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]);

回答4:

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