Does anyone have any suggestions on how to search for text within a cell?
such as
in cell A1, which contain a text 'I am a boy", then I would like to search
for text "boy" within cell A1, if cell A1 contains this text, then return 1
in cell B1, else return 0.
Does anyone have any suggestions
Thank you in advance
Eric

On Sat, 12 May 2007 03:52:00 -0700, Eric
wrote:

Does anyone have any suggestions on how to search for text within a cell?
such as
in cell A1, which contain a text 'I am a boy", then I would like to search
for text "boy" within cell A1, if cell A1 contains this text, then return 1
in cell B1, else return 0.
Does anyone have any suggestions
Thank you in advance
Eric

=COUNTIF(A1,"*boy*")
--ron

In B1 enter:

=IF(LEN(A1)=LEN(SUBSTITUTE(A1,"boy","")),0,1)

--
Gary''s Student - gsnu200721

among others
=if(iserror(find("boy",a1)),0,1)

"Eric" wrote:

Does anyone have any suggestions on how to search for text within a cell?
such as
in cell A1, which contain a text 'I am a boy", then I would like to search
for text "boy" within cell A1, if cell A1 contains this text, then return 1
in cell B1, else return 0.
Does anyone have any suggestions
Thank you in advance
Eric

Thank everyone a lot
Eric

Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula, such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the formula
contains "boy", else return 0.
Thank for any suggestions
Eric

You might care to try this, using XL 4.0 macros.

First a caveat:
This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to sheets
within
the existing WB.

So then ... say your link formula is in A1.
Start my creating a 'named' formula that will return the actual formula text
from A1;

Click in B1, then,
<Insert <Name <Define,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.

So, with A1 containing your formula of:
=C:\boy\[Data.xls]sheet!A1

In B1 enter:
=form

To get the contents of A1.
You'll see the text of your formula displayed.

NOW, simply wrap that in another formula to return the "1" you're looking
for:

=--ISNUMBER(SEARCH("boy",form))

Don't forget, the
=form
formula *only* looks at the cell in the *preceding* column to display it's
contents.

If you would want it to return the cell *2* columns over, click in C1,
instead of B1, when you enter the get.cell formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Eric" wrote in message
...
Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula, such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the
formula
contains "boy", else return 0.
Thank for any suggestions
Eric

Thank everyone very much for suggestions
Eric

"Ragdyer" wrote:

You might care to try this, using XL 4.0 macros.

First a caveat:
This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to sheets
within
the existing WB.

So then ... say your link formula is in A1.
Start my creating a 'named' formula that will return the actual formula text
from A1;

Click in B1, then,
<Insert <Name <Define,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.

So, with A1 containing your formula of:
=C:\boy\[Data.xls]sheet!A1

In B1 enter:
=form

To get the contents of A1.
You'll see the text of your formula displayed.

NOW, simply wrap that in another formula to return the "1" you're looking
for:

=--ISNUMBER(SEARCH("boy",form))

Don't forget, the
=form
formula *only* looks at the cell in the *preceding* column to display it's
contents.

If you would want it to return the cell *2* columns over, click in C1,
instead of B1, when you enter the get.cell formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Eric" wrote in message
...
Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula, such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the
formula
contains "boy", else return 0.
Thank for any suggestions
Eric

Thank everyone for your suggesitons

=GET.CELL(6,A1)
Could you please tell me what the number 6 mean in this formula?
Thank you
Eric


"Ragdyer" wrote:

You might care to try this, using XL 4.0 macros.

First a caveat:
This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to sheets
within
the existing WB.

So then ... say your link formula is in A1.
Start my creating a 'named' formula that will return the actual formula text
from A1;

Click in B1, then,
<Insert <Name <Define,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.

So, with A1 containing your formula of:
=C:\boy\[Data.xls]sheet!A1

In B1 enter:
=form

To get the contents of A1.
You'll see the text of your formula displayed.

NOW, simply wrap that in another formula to return the "1" you're looking
for:

=--ISNUMBER(SEARCH("boy",form))

Don't forget, the
=form
formula *only* looks at the cell in the *preceding* column to display it's
contents.

If you would want it to return the cell *2* columns over, click in C1,
instead of B1, when you enter the get.cell formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Eric" wrote in message
...
Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula, such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the
formula
contains "boy", else return 0.
Thank for any suggestions
Eric

You can download a file to give you information on the XL 4.0 macros:

http://tinyurl.com/seb4r

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Eric" wrote in message
...
Thank everyone for your suggesitons

=GET.CELL(6,A1)
Could you please tell me what the number 6 mean in this formula?
Thank you
Eric


"Ragdyer" wrote:

You might care to try this, using XL 4.0 macros.

First a caveat:
This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to
sheets
within
the existing WB.

So then ... say your link formula is in A1.
Start my creating a 'named' formula that will return the actual formula
text
from A1;

Click in B1, then,
<Insert <Name <Define,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK


What you have now is a 'relative' *named formula* that when entered in
*any*
cell, will return the contents of the cell (text, data, formulas) from
the
*previous* column.

So, with A1 containing your formula of:
=C:\boy\[Data.xls]sheet!A1

In B1 enter:
=form

To get the contents of A1.
You'll see the text of your formula displayed.

NOW, simply wrap that in another formula to return the "1" you're
looking
for:

=--ISNUMBER(SEARCH("boy",form))

Don't forget, the
=form
formula *only* looks at the cell in the *preceding* column to display
it's
contents.

If you would want it to return the cell *2* columns over, click in C1,
instead of B1, when you enter the get.cell formula.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Eric" wrote in message
...
Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula,
such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the
formula
contains "boy", else return 0.
Thank for any suggestions
Eric