Getting total of a particular word repeated more than once in a single cell

[VDS]

Dear All,

I attach herewith the extract of Tender Specification where the word "GIS" has been repeated more than once. I have done SEARCH 5 times till the starting number when equal to LENGTH OF A1.

Here I want to find how many times the GIS has been repeated with a single formula. Is it can be done with a single function ? Help is requested.


VDS

 

[Mike H..]

Dear All,

I attach herewith the extract of Tender Specification where the word "GIS" has been repeated more than once. I have done SEARCH 5 times till the starting number when equal to LENGTH OF A1.

Here I want to find how many times the GIS has been repeated with a single formula. Is it can be done with a single function ? Help is requested.


VDS

Hi,

Try this

=(SUM(LEN(UPPER(" "&A1&" ")))-SUM(LEN(SUBSTITUTE(UPPER(" "&A1&" ")," GIS ",))))/LEN(" Gis ")

 

[Faseeh]

This one too: =ABS(LEN(A1)-LEN(SUBSTITUTE(A1,"Gis"," ")))

 

[Syedali]

This one too: =ABS(LEN(A1)-LEN(SUBSTITUTE(A1,"Gis"," ")))

From your formula am getting 10. it is not correct answer bro.

 

[Marc L]

Hi,

try this : =(LEN(A1)- LEN(SUBSTITUTE(A1,"Gis","")))/3 …

Do you like it ? So thanks to click on bottom right Like !
​

 

[VDS]

@Dear All,

Awesome answer. Thanks a lot.

@Marc: Can u explain me, about this formula(LEN(A1)- LEN(SUBSTITUTE(A1,"Gis","")))/3 …. because all times "GIS" may not be correct even the length also different. How can I customize this function as per my need.

VDS

 

[Marc L]

Characters length of cell minus characters length of cell without word,
result divided by word characters length …
​

 

[Mike H..]

@Dear All,

Awesome answer. Thanks a lot.

@Marc: Can u explain me, about this formula(LEN(A1)- LEN(SUBSTITUTE(A1,"Gis","")))/3 …. because all times "GIS" may not be correct even the length also different. How can I customize this function as per my need.

VDS

Hi,

That method may work fine for you but a word of caution. If your search string is Gis for example the that method will find Gis if it's contained in a longer word such as Giste and return an incorrect number. The method I provided doesn't do that and will only find the search string.

Here it is again changed so that you can put your search string in B1

=(SUM(LEN(UPPER(" "&A1&" ")))-SUM(LEN(SUBSTITUTE(UPPER(" "&A1&" ")," "&UPPER(B1)&" ",""))))/LEN(" "&B1&" ")

 

[Marc L]

As you can have a problem for a sentence starting with the word
(no space before !) or with the last word of a sentence followed by a point
or even in the middle with a comma following word (no space after !) …
​