Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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!




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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!






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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!



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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!






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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!










  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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!



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to copy the first word or two words from a cell containing a complete sentence to another cell jonny Excel Discussion (Misc queries) 7 May 19th 23 03:43 AM
Link a cell to a word in a sentence Brenelder Excel Discussion (Misc queries) 1 March 14th 08 09:36 PM
how to keep a word of same sentence starting w a capital next lin dottie Excel Worksheet Functions 1 September 20th 07 05:32 PM
separate a Capital starting word from a sentence Rasoul Khoshravan Excel Worksheet Functions 3 October 25th 06 06:31 PM
How do I highlight a word or sentence within Excel? cands Excel Discussion (Misc queries) 1 October 11th 05 10:58 PM


All times are GMT +1. The time now is 01:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"