Find a word in a sentence
 

I searched the board, but couldn't find an exact answer to my issue, so I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in message
...
I searched the board, but couldn't find an exact answer to my issue, so I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Hi,

=IF(AND(ISTEXT(Sheet2!A1),ISERROR(SEARCH("Already" ,Sheet2!A1))),"X","")

Mike

"Chicago2Paris" wrote:

I searched the board, but couldn't find an exact answer to my issue, so I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet 1!A1))),"x","")

Ooops!

I left out the sheet reference in the ISTEXT function. Should be :

=IF(AND(ISTEXT(Sheet1!A1),ISERROR(SEARCH("Already" ,Sheet1!A1))),"x","")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in message
...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Here is another way...

=IF(A1="","",LEFT("X",COUNTIF(A1,"*already*")=0))

--
Rick (MVP - Excel)


"Chicago2Paris" wrote in message
...
I searched the board, but couldn't find an exact answer to my issue, so I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Shouldn't your Sheet1 reference go on the A1 in the ISTEXT function call as
well?

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet 1!A1))),"x","")

Ooops!

I left out the sheet reference in the ISTEXT function. Should be :

=IF(AND(ISTEXT(Sheet1!A1),ISERROR(SEARCH("Already" ,Sheet1!A1))),"x","")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in
message ...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Two things...

First, I left out the sheet reference, so my post should have been this...

=IF(Sheet1!A1="","",LEFT("X",COUNTIF(Sheet1!A1,"*a lready*")=0))

Second, if you *really* meant "text" as your post said, meaning pure numbers
should not return an "X", then use this instead...

=IF(ISNUMBER(Sheet1!A1),"",LEFT("X",COUNTIF(Sheet1 !A1,"*already*")=0))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is another way...

=IF(A1="","",LEFT("X",COUNTIF(A1,"*already*")=0))

--
Rick (MVP - Excel)


"Chicago2Paris" wrote in message
...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Never mind... I misread your posting.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Shouldn't your Sheet1 reference go on the A1 in the ISTEXT function call
as well?

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet 1!A1))),"x","")

Ooops!

I left out the sheet reference in the ISTEXT function. Should be :

=IF(AND(ISTEXT(Sheet1!A1),ISERROR(SEARCH("Already" ,Sheet1!A1))),"x","")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in
message ...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Yes, I corrected that. Unless I'm not following you on this?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Shouldn't your Sheet1 reference go on the A1 in the ISTEXT function call
as well?

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet 1!A1))),"x","")

Ooops!

I left out the sheet reference in the ISTEXT function. Should be :

=IF(AND(ISTEXT(Sheet1!A1),ISERROR(SEARCH("Already" ,Sheet1!A1))),"x","")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in
message ...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

It was my mistake... I accidentally read your referenced line (the one with
the '' symbol in front of it) as the formula you were posting and responded
to that.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Yes, I corrected that. Unless I'm not following you on this?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Shouldn't your Sheet1 reference go on the A1 in the ISTEXT function call
as well?

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet 1!A1))),"x","")

Ooops!

I left out the sheet reference in the ISTEXT function. Should be :

=IF(AND(ISTEXT(Sheet1!A1),ISERROR(SEARCH("Already" ,Sheet1!A1))),"x","")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

=IF(AND(ISTEXT(A1),ISERROR(SEARCH("Already",Sheet1 !A1))),"x","")

--
Biff
Microsoft Excel MVP


"Chicago2Paris" wrote in
message ...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!

Find a word in a sentence
 

Thanks for all the help... works like a charm now.



"Rick Rothstein" wrote:

Two things...

First, I left out the sheet reference, so my post should have been this...

=IF(Sheet1!A1="","",LEFT("X",COUNTIF(Sheet1!A1,"*a lready*")=0))

Second, if you *really* meant "text" as your post said, meaning pure numbers
should not return an "X", then use this instead...

=IF(ISNUMBER(Sheet1!A1),"",LEFT("X",COUNTIF(Sheet1 !A1,"*already*")=0))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is another way...

=IF(A1="","",LEFT("X",COUNTIF(A1,"*already*")=0))

--
Rick (MVP - Excel)


"Chicago2Paris" wrote in message
...
I searched the board, but couldn't find an exact answer to my issue, so
I'm
looking for some help.

I have a column that may contain text or may be blank.

I want to place an "x" on another sheet if:
1. There is text in the cell AND
2. The cell does not contain the word "Already" in the sentence

Thanks!


.