Finding Partial Text in a Cell
 

I want to check a cell for the word "Total." The cell contains additional
text before the word Total. If the Total is found, I want to populate the
cell containing the formula with 1. If not, populate it with 0.

What is the formula for this?

The IF fundtion doesn't seem to include a way to find partial text strings.

Hi,


=COUNTIF(A1,"*Total")

or

=COUNTIF(A1,"*Total*")


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

=IF(ISNUMBER(SEARCH("Total",A1)),1,0)

or without IF

=--ISNUMBER(SEARCH("Total",A1))


Regards,

Peo Sjoblom


"bob" wrote:

I want to check a cell for the word "Total." The cell contains additional
text before the word Total. If the Total is found, I want to populate the
cell containing the formula with 1. If not, populate it with 0.

What is the formula for this?

The IF fundtion doesn't seem to include a way to find partial text strings.

Hi
try
=--ISNUMBER(FIND("Total",A1))

--
Regards
Frank Kabel
Frankfurt, Germany

bob wrote:
I want to check a cell for the word "Total." The cell contains
additional text before the word Total. If the Total is found, I want
to populate the cell containing the formula with 1. If not, populate
it with 0.

What is the formula for this?

The IF fundtion doesn't seem to include a way to find partial text
strings.

=if(search("Total",a1)=1,1,0)
where cell A1 contains the text
"bob" wrote in message
...
I want to check a cell for the word "Total." The cell contains additional
text before the word Total. If the Total is found, I want to populate the
cell containing the formula with 1. If not, populate it with 0.

What is the formula for this?

The IF fundtion doesn't seem to include a way to find partial text
strings.

"Nitin Harkawat" wrote...
=if(search("Total",a1)=1,1,0)
where cell A1 contains the text
....

If A1 doesn't contain the substring Total, SEARCH will return a #VALUE!
error, so your formula would evaluate to #VALUE! rather than 0.

"bob" wrote...
I want to check a cell for the word "Total." The cell contains additional
text before the word Total. If the Total is found, I want to populate the
cell containing the formula with 1. If not, populate it with 0.
....

If Total always appears at the end of the string, try

=--(RIGHT(TRIM(x),5)="Total")

Otherwise, another alternative using only one function call,

=--(SUBSTITUTE(x,"Total","")<x)