A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding text



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2009, 07:27 PM posted to microsoft.public.excel.newusers
John
external usenet poster
 
Posts: 2,649
Default Finding text

I am using Excel 2007, I have a column containing formulas that would return
a name if true and a zero if false. I want to search the column for the text
entry and return that text as the answer. Can I do this or should the column
formulas be changed to something else? The "T" formula seems to work only
for one cell not the whole column.
  #2  
Old March 4th, 2009, 11:21 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Finding text

It's not real clear what you want to do. Here's my best guess...

Assume you want to know if John is in the range.

=IF(COUNTIF(A1:A100,"John"),"John","not found")

Better to use a cell to hold the criteria:

C1 = John

=IF(COUNTIF(A1:A100,C1),C1,"not found")

--
Biff
Microsoft Excel MVP


"JOHN" wrote in message
...
I am using Excel 2007, I have a column containing formulas that would
return
a name if true and a zero if false. I want to search the column for the
text
entry and return that text as the answer. Can I do this or should the
column
formulas be changed to something else? The "T" formula seems to work only
for one cell not the whole column.



  #3  
Old March 5th, 2009, 09:25 AM posted to microsoft.public.excel.newusers
DILipandey
external usenet poster
 
Posts: 69
Default Finding text

Hi John,

Assuming the text you want to search is there in Cell C1, and the data in
which search is to be conducted is there in Column A, Now use the following
formula:-

=IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is
Not There")

Let me know if it works.. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"JOHN" wrote:

I am using Excel 2007, I have a column containing formulas that would return
a name if true and a zero if false. I want to search the column for the text
entry and return that text as the answer. Can I do this or should the column
formulas be changed to something else? The "T" formula seems to work only
for one cell not the whole column.

  #4  
Old March 5th, 2009, 02:51 PM posted to microsoft.public.excel.newusers
John
external usenet poster
 
Posts: 2,649
Default Finding text

Let me try to make it clearer. column A contains names, column B contains
sales totals, below in column B is a MIN formula, if the sales total equals
the MIN, the formula in column C returns the name, if not a zero. I want the
formula to search column C to find the text entry (name) and return that name
as the result. In Column C there will be only one text entry and the rest
numerical. The text could be in any cell in column C.

"DILipandey" wrote:

Hi John,

Assuming the text you want to search is there in Cell C1, and the data in
which search is to be conducted is there in Column A, Now use the following
formula:-

=IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is
Not There")

Let me know if it works.. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"JOHN" wrote:

I am using Excel 2007, I have a column containing formulas that would return
a name if true and a zero if false. I want to search the column for the text
entry and return that text as the answer. Can I do this or should the column
formulas be changed to something else? The "T" formula seems to work only
for one cell not the whole column.

  #5  
Old March 5th, 2009, 03:43 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Finding text

the formula in column C returns the name, if not a zero
there will be only one text entry and the rest numerical.


Ok, that clears it up nicely!

Try this:

=INDEX(C2:C20,MATCH("*",C2:C20,0))

Adjust the range to suit.

--
Biff
Microsoft Excel MVP


"JOHN" wrote in message
...
Let me try to make it clearer. column A contains names, column B contains
sales totals, below in column B is a MIN formula, if the sales total
equals
the MIN, the formula in column C returns the name, if not a zero. I want
the
formula to search column C to find the text entry (name) and return that
name
as the result. In Column C there will be only one text entry and the rest
numerical. The text could be in any cell in column C.

"DILipandey" wrote:

Hi John,

Assuming the text you want to search is there in Cell C1, and the data in
which search is to be conducted is there in Column A, Now use the
following
formula:-

=IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name
Is
Not There")

Let me know if it works.. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"JOHN" wrote:

I am using Excel 2007, I have a column containing formulas that would
return
a name if true and a zero if false. I want to search the column for the
text
entry and return that text as the answer. Can I do this or should the
column
formulas be changed to something else? The "T" formula seems to work
only
for one cell not the whole column.



  #6  
Old March 5th, 2009, 04:07 PM posted to microsoft.public.excel.newusers
John
external usenet poster
 
Posts: 2,649
Default Finding text

Thank you Biff,
That works perfectly, just what I was looking for. That you all for your
input, the success of my worksheet is the direct result of the people here
taking the time to help others. Thank you all again.

"T. Valko" wrote:

the formula in column C returns the name, if not a zero
there will be only one text entry and the rest numerical.


Ok, that clears it up nicely!

Try this:

=INDEX(C2:C20,MATCH("*",C2:C20,0))

Adjust the range to suit.

--
Biff
Microsoft Excel MVP


"JOHN" wrote in message
...
Let me try to make it clearer. column A contains names, column B contains
sales totals, below in column B is a MIN formula, if the sales total
equals
the MIN, the formula in column C returns the name, if not a zero. I want
the
formula to search column C to find the text entry (name) and return that
name
as the result. In Column C there will be only one text entry and the rest
numerical. The text could be in any cell in column C.

"DILipandey" wrote:

Hi John,

Assuming the text you want to search is there in Cell C1, and the data in
which search is to be conducted is there in Column A, Now use the
following
formula:-

=IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name
Is
Not There")

Let me know if it works.. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"JOHN" wrote:

I am using Excel 2007, I have a column containing formulas that would
return
a name if true and a zero if false. I want to search the column for the
text
entry and return that text as the answer. Can I do this or should the
column
formulas be changed to something else? The "T" formula seems to work
only
for one cell not the whole column.




  #7  
Old March 5th, 2009, 04:32 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default Finding text

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JOHN" wrote in message
news
Thank you Biff,
That works perfectly, just what I was looking for. That you all for your
input, the success of my worksheet is the direct result of the people here
taking the time to help others. Thank you all again.

"T. Valko" wrote:

the formula in column C returns the name, if not a zero
there will be only one text entry and the rest numerical.


Ok, that clears it up nicely!

Try this:

=INDEX(C2:C20,MATCH("*",C2:C20,0))

Adjust the range to suit.

--
Biff
Microsoft Excel MVP


"JOHN" wrote in message
...
Let me try to make it clearer. column A contains names, column B
contains
sales totals, below in column B is a MIN formula, if the sales total
equals
the MIN, the formula in column C returns the name, if not a zero. I
want
the
formula to search column C to find the text entry (name) and return
that
name
as the result. In Column C there will be only one text entry and the
rest
numerical. The text could be in any cell in column C.

"DILipandey" wrote:

Hi John,

Assuming the text you want to search is there in Cell C1, and the data
in
which search is to be conducted is there in Column A, Now use the
following
formula:-

=IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is
There","Name
Is
Not There")

Let me know if it works.. Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"JOHN" wrote:

I am using Excel 2007, I have a column containing formulas that
would
return
a name if true and a zero if false. I want to search the column for
the
text
entry and return that text as the answer. Can I do this or should
the
column
formulas be changed to something else? The "T" formula seems to
work
only
for one cell not the whole column.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:40 PM.


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