I don't think that'll work. In the U.S. we have 2 kinds of postal zip codes,
a 5 digit number and what they call zip+4 which is a 5 digit number followed
by a hyphen and 4 more digits.
12345
12345-1234
In addition to those the OP also said they had what is the equivalent of the
zip+4 without the hyphen or a 9 digit number.
12345
12345-1234
123451234
So, if they were looking for the 5 digit zip12345, that formula would give a
false positive because it would find 12345 in a code like 123412345
--
Biff
Microsoft Excel MVP
"Ashish Mathur" wrote in message
...
Hi,
You can also use the following array formula (Ctrl+Shift+Enter)
COUNT(SEARCH(D1,A1:A2,1))
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Don" wrote in message
...
A Zip Code list may contain: 12345 and 12345-6789 and 123456789
The reference cell "D1" on a seperate sheet = 12345
I want to know the number of occurances of the value in "D1" in the Zip
Code
List and I need to fill down 1800 rows (D1 to D1800) with the formula.
=COUNTIF('10 yrs'!I:I,D1) returns a count of 12345 within the list but
does
not capture the occurance of 12345 within the other longer strings. I
cannot
seem to add a wildcard to the "D1" value.
Any thought on how I can get there?