Counting numbers withing a mixed text/number cell

[Jared Croft]

I am trying to count the number of times certain zip codes appear in a
mass mailing list. COUNT or COUNTA would work except for the fact
that the zip codes are not in separate cells - they are in the same cell as
the City and State information.

If there is a way to separate the zip from the rest and then count it that
would be great. If there is a way to count the zip codes while still in the
same cells as the city and state info that would be better.

Jared

 

[Debra Dalgleish]

With your city/state/zip information in cells C2:C36, and the zip code
that you want to count in cell K1, enter the following formula in cell K2:

=SUMPRODUCT(ISNUMBER(SEARCH(K1,$C$2:$C$36))*($C$2:$C$36<>""))

 

[Ken Wright]

Or just using the double unary to coerce numeric:-

=SUMPRODUCT(--ISNUMBER(SEARCH(K1,$C$2:$C$36)))