Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC" characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

On Mon, 25 Jun 2007 19:45:00 -0700, Eric
wrote:

Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC" characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric


=IF(ISNUMBER(FIND("ABC",A1)),"ABC","")


--ron

=IF(ISNUMBER(SEARCH("ABC",A1)),"yes","")


"Eric" wrote:

Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC" characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

correction:

=IF(ISNUMBER(SEARCH("ABC",A1)),"ABC","")


"Teethless mama" wrote:

=IF(ISNUMBER(SEARCH("ABC",A1)),"yes","")


"Eric" wrote:

Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC" characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

Do you want to know if the string "ABC" is part of the *formula* and not the
*result* of the formula?

A1 contains this formula:

='C:\ABC\[Sample.xls]Sheet1'!A1

Create this named formula:

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

Then enter this formula in B1:

=IF(ISNUMBER(SEARCH("ABC",Formula)),"ABC","")

Biff

"Eric" wrote in message
...
Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC"
characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

On Mon, 25 Jun 2007 19:45:00 -0700, Eric
wrote:

Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC" characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

Just a note.

My formula, using FIND, is case sensitive. Teethless Mama's formula, using
SEARCH, is case insensitive.
--ron

Thank everyone for suggestions

Yes, I want to know if the string "ABC" is part of the *formula* and not the
*result* of the formula?

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

It works when I insert a path in cell A1 and check it in cell B1, but it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it on cell
J1.
Do I need to change any following parameter in order to make it work for my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric


"T. Valko" wrote:

Do you want to know if the string "ABC" is part of the *formula* and not the
*result* of the formula?

A1 contains this formula:

='C:\ABC\[Sample.xls]Sheet1'!A1

Create this named formula:

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

Then enter this formula in B1:

=IF(ISNUMBER(SEARCH("ABC",Formula)),"ABC","")

Biff

"Eric" wrote in message
...
Under cell A1, which contains a link to file C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC"
characters
inside this path, if yes, then return String "ABC", else nothing "".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

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

Yes, I want to know if the string "ABC" is part of the *formula* and not
the
*result* of the formula?

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

It works when I insert a path in cell A1 and check it in cell B1, but it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it on
cell
J1.
Do I need to change any following parameter in order to make it work for
my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric


This would be best done with a user defined function but I'm not the best
programmer!

If the target formula is in cell B2 and you want the extraction formula in
cell J1:

Change:

Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))

To:

=GET.CELL(6,INDIRECT("R[1]C[-8]",FALSE))

If the extraction formula is used for only this one specific cell reference,
Sheet1 B2:

=GET.CELL(6,Sheet1!$B$2)

Using the R1C1 reference style as I did gives it a little bit more
flexibilty as it can be used in other cells. Using absolute A1 reference
style limits its use to only one cell. Using relative A1 reference style can
lead to problems.

Biff

Thank everyone for suggestions
Eric

"T. Valko" wrote:


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

Yes, I want to know if the string "ABC" is part of the *formula* and not
the
*result* of the formula?

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

It works when I insert a path in cell A1 and check it in cell B1, but it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it on
cell
J1.
Do I need to change any following parameter in order to make it work for
my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric


This would be best done with a user defined function but I'm not the best
programmer!

If the target formula is in cell B2 and you want the extraction formula in
cell J1:

Change:

Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))

To:

=GET.CELL(6,INDIRECT("R[1]C[-8]",FALSE))

If the extraction formula is used for only this one specific cell reference,
Sheet1 B2:

=GET.CELL(6,Sheet1!$B$2)

Using the R1C1 reference style as I did gives it a little bit more
flexibilty as it can be used in other cells. Using absolute A1 reference
style limits its use to only one cell. Using relative A1 reference style can
lead to problems.

Biff

Hi

If you are wanting to know that it is part of a path, then modifying
Ron's formula to
=IF(ISNUMBER(FIND("\ABC\",B2)),"ABC","")
will do that for you.

Or to return the first part of the path, regardless of what the path is,
then
=MID(B2,FIND("\",B2)+1,FIND("^^",(SUBSTITUTE(B2,"\ ","^^",2)))-1-FIND("\",B2))

--
Regards

Roger Govier


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

Yes, I want to know if the string "ABC" is part of the *formula* and
not the
*result* of the formula?

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

It works when I insert a path in cell A1 and check it in cell B1, but
it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it
on cell
J1.
Do I need to change any following parameter in order to make it work
for my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric


"T. Valko" wrote:

Do you want to know if the string "ABC" is part of the *formula* and
not the
*result* of the formula?

A1 contains this formula:

='C:\ABC\[Sample.xls]Sheet1'!A1

Create this named formula:

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

Then enter this formula in B1:

=IF(ISNUMBER(SEARCH("ABC",Formula)),"ABC","")

Biff

"Eric" wrote in message
...
Under cell A1, which contains a link to file
C:\ABC\[Sample.xls]Sheet1!A1.
In cell B1, I would like to detect whether cell A1 contains "ABC"
characters
inside this path, if yes, then return String "ABC", else nothing
"".

Does anyone have any suggestions?
Thank in advance for any suggestions
Eric

I'd use a UDF, too!

Option Explicit
Function FormulaContains(rng As Range, myStr As String) As Boolean
Set rng = rng.Cells(1)
FormulaContains = False
If rng.HasFormula Then
FormulaContains _
= CBool(InStr(1, rng.Formula, myStr, vbTextCompare) 0)
End If
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=formulacontains(a1, "abc")



"T. Valko" wrote:

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

Yes, I want to know if the string "ABC" is part of the *formula* and not
the
*result* of the formula?

InsertNameDefine
Name: Formula
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
OK

It works when I insert a path in cell A1 and check it in cell B1, but it
does not work if I use this approach in another cell.
My case is that I need to search the path formula in B2 and check it on
cell
J1.
Do I need to change any following parameter in order to make it work for
my
case?
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))
Do you have any suggestions?
Thank everyone very much for any suggestions
Eric


This would be best done with a user defined function but I'm not the best
programmer!

If the target formula is in cell B2 and you want the extraction formula in
cell J1:

Change:

Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))

To:

=GET.CELL(6,INDIRECT("R[1]C[-8]",FALSE))

If the extraction formula is used for only this one specific cell reference,
Sheet1 B2:

=GET.CELL(6,Sheet1!$B$2)

Using the R1C1 reference style as I did gives it a little bit more
flexibilty as it can be used in other cells. Using absolute A1 reference
style limits its use to only one cell. Using relative A1 reference style can
lead to problems.

Biff

--

Dave Peterson

Thank everyone very much for suggestions
Eric

Eric wrote on Tue, 26 Jun 2007 06:17:03 -0700:

E Thank everyone very much for suggestions
E Eric

Eh? Conveys no information but I'm glad you are thankful :-)

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not