Count after finding a Word within a Cell

[RedDodger78]

Hi,

I am hoping that someone can help to find a solution.

In the example text below, what I trying to do is find the words: Bas, Maj & Repair, when I do this using the formula below it returns the number of times that the word appears, however, what I really need to do is to count the number of items between the dash and the semi-colon after the word and to return that number for each instance.

Example text:

[TABLE="width: 575"]
<tbody>[TR]
[TD="class: xl63, width: 575"]BAS - PROT; MAJ - CAB: MAJ - X301; BAS - X228, X303, X324, CT, VT; MAJ - X201C, X201D, X201E; MAJ - X201A, X201B; BAS - X128, CT, PROT; REPAIR - X143G4, X143G2; MAJ - 1SE, 1SH; BAS - X120, X124, VT, PROT; BAS - PROT;[/TD]
[/TR]
</tbody>[/TABLE]

I am using the following formula to count the number of words within a cell:

=SUMPRODUCT((LEN($A1)-LEN(SUBSTITUTE((UPPER($A1)),UPPER("Bas"),"")))/LEN("Bas"))

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]BAS =[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[/TR]
[TR]
[TD]MAJ =[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[/TR]
[TR]
[TD]Repair =[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]

What I would really like to do is to count the number of items that are after this word and between the dash and the semi-colon so I end up with:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]BAS =[/TD]
[TD="class: xl65, width: 64"]14[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD]MAJ =[/TD]
[TD="class: xl65, width: 64"]8[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]Repair =[/TD]
[TD="class: xl65, width: 64"]2[/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

I am hoping that this is possible as I have around 4,000+ cells that I am trying to analyse on a regular basis, and doing this manually is very time consuming!

Thank you in advance for any support

 

[James006]

Hi,

For so many cells... 4'000 + ... why not creating a customized UDF ...?

 

[RedDodger78]

Hi James006

Thank you foryour response.

I'm not familiar as yet with VBA.