How to detect characters with a cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...


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


--ron
 
correction:

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


Teethless mama said:
=IF(ISNUMBER(SEARCH("ABC",A1)),"yes","")


Eric said:
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
Click to expand...
Click to expand...
 
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:

Insert>Name>Define
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
 
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
Click to expand...

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?

Insert>Name>Define
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 said:
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:

Insert>Name>Define
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 said:
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
Click to expand...
Click to expand...
 
Eric said:
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?

Insert>Name>Define
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
Click to expand...

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))
Click to expand...

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 said:
Eric said:
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?

Insert>Name>Define
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
Click to expand...

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))
Click to expand...

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
Click to expand...
 
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 said:
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?

Insert>Name>Define
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 said:
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:

Insert>Name>Define
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Eric said:
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?

Insert>Name>Define
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
Click to expand...

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))
Click to expand...

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
Click to expand...
 
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
 
Back
Top