Counting word types in a cell

[Wes_SA]

If the text below was contained in one cell in Excel, how do I count the
instances of say "PFD". If this can be done how do I then count for multiple
cells.


15-PR-11-PFD-001 REV.0A
15-PR-13-PFD-001 REV.0A
15-PR-17-PFD-002 REV.0A
15-PR-18-PFD-001 REV.0A
15-PR-11-PID-014 REV.0A
15-PR-11-PID-022 REV.1A
15-PR-11-PID-023 REV.1A
15-PR-17-PID-003 REV.1A
15-PR-13-PID-029 REV.1A
15-PR-11-PID-030 REV.1A

 

[Dave Peterson]

Is each of those lines (like: 15-PR-11-PFD-001 REV.0A) in a single cell?

Or is it a bunch of that stuff in a single cell?

If your data was in column A, then these will count the number of cells with at
least 1 pfd in it:
=countif(a:a,"*pfd*")
or
=countif(a:a,"*"&"pfd"&"*")
or if PFD was in a different cell:
=countif(a:a,"*"&b1&"*")

The asterisk is a wildcard and represents any number (including 0) of
characters.

===========
If that whole mess were in a single cell, you could count the number of PFD's in
that cell:

=(len(a1)-len(substitute(upper(a1),Upper("pfd"),"")))/len("pfd")

To count all the number of those PFD's, you could use:
=sumproduct((len(a1:a10)-len(substitute(upper(a1:a10),upper("pfd"),"")))
/len("pfd"))

You can't use the entire column unless you're using xl2007.

=substitute() is case sensitive. By wrapping both the original and string with
=upper(), the formula counts all the PFD's--upper/lower/combination case.

 

[Wes_SA]

Dave

Thanks very much, solved my problem in one shot.

Regards

Wes_SA