string/char CONTAINS function?

[jim sturtz]

i am trying to determine if a cell has a partial value in it, for example
cola colb
john smith 1
john brown 2
bob smith 3
carly simon 4
john clark 5
tom smith 6


processing column a, top to bottom,for 'smith' would return 10, or 'john'
would return 8.

any help would be appreciated.

btw, if column a is a currency field can you find the '$'. i tried the
search function on that kind of field and it doesnt think it is text so
struck out.

can you 'convert' a cell to text for comparison purposes without having to
go change the true underlying format?

thanks.

jim

microsoft.public.excel.worksheet.functions

 

[Dave Peterson]

=countif(a:a,"*smith*")
or
=countif(a:a,"*"&b1&"*")
if b1 contained Smith.

===
If I looked in values (not formulas) under the Options button on the Edit|Find
dialog, I could find the $ in cells that were formatted as Currency.

===

You may be able to adjust your formula to make it use text:
For instance:
=vlookup(""&a1, sheet2!a:c,3,false)
or
=vlookup(text(a1,"000.00"),sheet2!a:c,3,false)

 

[Gary''s Student]

Use a combination of autofilter and the SUBTOTAL() function:

1. put a header cell on top of each column
2. click the header cell and pull down:
Data > Filter > Autofilter > Custom and select contains smith
3. this should display only the smith rows
4. in an un-used cell enter:
=SUBTOTAL(9,B2:B100)


This form of SUBTOTAL will add-up the values in a column, but ignore cells
filtered out.

 

[jim sturtz]

thanks for the quick replies.

Use a combination of autofilter and the SUBTOTAL() function:

1. put a header cell on top of each column
2. click the header cell and pull down:
Data > Filter > Autofilter > Custom and select contains smith
3. this should display only the smith rows
4. in an un-used cell enter:
=SUBTOTAL(9,B2:B100)


This form of SUBTOTAL will add-up the values in a column, but ignore cells
filtered out.