needed formula

  • Thread starter Thread starter Mikey
  • Start date Start date
M

Mikey

I have a cell containing a string of text, we'll call it cell A1.
I need to put a formula in C1 that will search for only a part of the
text in A1 and, if found, will return the value in B1, or, if not
found, will return a value of zero (0).

I've tried SEARCH in combination with IF, but, if not found, keeps
returning the #VALUE! error symbol, which SEARCH does so if the string
of text is not found.

It would be used in my electronic checkbooks. Look for a part of the
text such as "Exxon" in the check or transaction description and, if
found, return the amount of the transaction in a new cell to be
further itemized.

Don't know what else to try.

-Mike
 
Try this formula in C1:

=IF(ISERROR(SEARCH("Exxon",A1)),0,B1)

This will return 0 if Exxon is not found in A1, but if it is found
then it returns the value in B1.

Hope this helps.

Pete
 
A1: (some text)

This formula returns the value in B1 if the
text "FindMe" appears anywhere in cell A1,
otherwise, it returns 0.

C1: =IF(COUNTIF(A1,"*FindMe*"),B1,0)

Another way....
D1: (text to find....eg FindMe)
C1: =IF(COUNTIF(A1,"*"&D1&"*"),B1,0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
I have a cell containing a string of text, we'll call it cell A1.
I need to put a formula in C1 that will search for only a part of the
text in A1 and, if found, will return the value in B1, or, if not
found, will return a value of zero (0).

I've tried SEARCH in combination with IF, but, if not found, keeps
returning the #VALUE! error symbol, which SEARCH does so if the string
of text is not found.

It would be used in my electronic checkbooks. Look for a part of the
text such as "Exxon" in the check or transaction description and, if
found, return the amount of the transaction in a new cell to be
further itemized.

Don't know what else to try.

-Mike
Click to expand...


=COUNTIF(A1,"*EXXON*")

(This is NOT case sensitive, so it will return 1 for Exxon, exxon, etc)
--ron
 
I have a cell containing a string of text, we'll call it cell A1.
I need to put a formula in C1 that will search for only a part of the
text in A1 and, if found, will return the value in B1, or, if not
found, will return a value of zero (0).

I've tried SEARCH in combination with IF, but, if not found, keeps
returning the #VALUE! error symbol, which SEARCH does so if the string
of text is not found.

It would be used in my electronic checkbooks. Look for a part of the
text such as "Exxon" in the check or transaction description and, if
found, return the amount of the transaction in a new cell to be
further itemized.

Don't know what else to try.

-Mike
Click to expand...

Sorry, I misread. Try this instead:

=SUMIF(A1,"*EXXON*",B1)


--ron
 
Back
Top