How to search for text within a cell?

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

In B1 enter:

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

 

[Guest]

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

 

[Guest]

Thank everyone a lot
Eric

 

[Guest]

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

 

[Ragdyer]

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.

 

[Guest]

Thank everyone very much for 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 !
---------------------------------------------------------------------------

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

 

[Guest]

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

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 !
---------------------------------------------------------------------------

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

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

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

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

!

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

-

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