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:

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;

<tbody>
</tbody>

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"))

BAS =	5
MAJ =	5
Repair =	1

<tbody>
</tbody>

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:

BAS =	14
MAJ =	8
Repair =	2

<tbody>
</tbody>

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.