Excel formula to search within the string and count repetition

[Jyotirmaya]

I want an excel formula to identify the specific word "to" from a cell within the text multiple times, there is a space after the word "to". Please help me with the code.

 

[Rick Rothstein]

Here is the long version so you can see how it works (in case you want to search for other words)...

=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&A1&" "," to ","")))/LEN(" to ")

and here is the short version making use of information we know...

=(2+LEN(A1)-LEN(SUBSTITUTE(" "&A1&" "," to ","")))/4

Note: SUBSTITUTE is case sensitive, so if you want this to work even when the search word begins a sentence and thus starts with a capital letter, you would need to do this...

Long version...
=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&UPPER(A1)&" ",UPPER(" to "),"")))/LEN(" to ")

Short version...
=(2+LEN(A1)-LEN(SUBSTITUTE(" "&UPPER(A1)&" "," TO ","")))/4

 

[Jyotirmaya]

Here is the long version so you can see how it works (in case you want to search for other words)...

=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&A1&" "," to ","")))/LEN(" to ")

and here is the short version making use of information we know...

=(2+LEN(A1)-LEN(SUBSTITUTE(" "&A1&" "," to ","")))/4

Note: SUBSTITUTE is case sensitive, so if you want this to work even when the search word begins a sentence and thus starts with a capital letter, you would need to do this...

Long version...
=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&UPPER(A1)&" ",UPPER(" to "),"")))/LEN(" to ")

Short version...
=(2+LEN(A1)-LEN(SUBSTITUTE(" "&UPPER(A1)&" "," TO ","")))/4

Thank you so much.