string/char CONTAINS function?

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

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




jim sturtz wrote:
>
> 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

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.


--
Gary's Student


"jim sturtz" wrote:

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

thanks for the quick replies.

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> --
> Gary's Student
>
>
> "jim sturtz" wrote:
>
> > 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
> >
> >
> >