Formula to read formula in a cell
 

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,

Formula to read formula in a cell
 

First install the following User Defined Function:

Public Function txet(r As Range) As String
txet = r.Formula
End Function

and then in B1:

=LEFT(RIGHT(txet(A1),LEN(txet(A1))-1),FIND("+",txet(A1))-2)


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx




--
Gary''s Student - gsnu200800


" wrote:

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,

Formula to read formula in a cell
 

You can convert a working XL formula into Text that can then be parsed to
return whatever part of the formula you need.
This is easily accomplished by incorporating an old XL 4.0 macro into a
*relative* named formula.

We'll configure this named formula to reference the column just to the left
of *any cell* containing this formula, though it can be configured to work
on
*any* cell that's relative to the location of the formula containing cell.

Click in B1.
Doesn't matter if it's empty or populated.
From the Menu Bar:
<Insert. <Name <Define


In the "Names In Workbook" box, enter a short name for this formula,
let's use

disp

to denote display.

Then, change whatever's in the "Refers To" box to:


=Get.Cell(6,A1)


Then <OK


With a *relative* reference to A1 (no $ signs), and since you clicked in B1
at the start, this formula will work on any cell to the immediate left of
*any* cell that contains this formula.


So, say in C1 you entered:
Tyson
In D1 enter
=disp
and you'll get "Tyson" returned.


Now, in C2 enter the formula:
=A1+A2

and in D2 enter:
=disp
And you'll get the formula returned,

=A1+A2

*not* the results of the formula.


Now, to use a Text formula to parse the formula itself:

Enter this formula in any cell to the immediate right of the cell containing
the formula you wish to parse:

=MID(disp,2,FIND("+",disp)-2)

This will return the characters between the equal sign and the first + sign.


--
HTH,

RD

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



wrote in message
...

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,

Formula to read formula in a cell
 

Forgot the caveat!

XL 4.0 macros can be used in all versions of XL,
*BUT*
In versions before XL02, you should *not* copy them between *workbooks*!
They *can* be copied between sheets within a workbook.

The versions prior to XL02 will crash if an attempt is made to copy between
*workbooks*.

--
Regards,

RD

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

"RagDyer" wrote in message
...
You can convert a working XL formula into Text that can then be parsed to
return whatever part of the formula you need.
This is easily accomplished by incorporating an old XL 4.0 macro into a
*relative* named formula.

We'll configure this named formula to reference the column just to the
left
of *any cell* containing this formula, though it can be configured to work
on
*any* cell that's relative to the location of the formula containing cell.

Click in B1.
Doesn't matter if it's empty or populated.
From the Menu Bar:
<Insert. <Name <Define


In the "Names In Workbook" box, enter a short name for this formula,
let's use

disp

to denote display.

Then, change whatever's in the "Refers To" box to:


=Get.Cell(6,A1)


Then <OK


With a *relative* reference to A1 (no $ signs), and since you clicked in
B1
at the start, this formula will work on any cell to the immediate left of
*any* cell that contains this formula.


So, say in C1 you entered:
Tyson
In D1 enter
=disp
and you'll get "Tyson" returned.


Now, in C2 enter the formula:
=A1+A2

and in D2 enter:
=disp
And you'll get the formula returned,

=A1+A2

*not* the results of the formula.


Now, to use a Text formula to parse the formula itself:

Enter this formula in any cell to the immediate right of the cell
containing the formula you wish to parse:

=MID(disp,2,FIND("+",disp)-2)

This will return the characters between the equal sign and the first +
sign.


--
HTH,

RD

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



wrote in message
...

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,

Formula to read formula in a cell
 

Great! Thanks for the help!!

On Aug 19, 12:34*pm, "RagDyer" wrote:
Forgot the caveat!

XL 4.0 macros can be used in all versions of XL,
*BUT*
In versions before XL02, you should *not* copy them between *workbooks*!
They *can* be copied between sheets within a workbook.

The versions prior to XL02 will crash if an attempt is made to copy between
*workbooks*.

--
Regards,

RD

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

"RagDyer" wrote in message

...



You can convert a working XL formula into Text that can then be parsed to
return whatever part of the formula you need.
This is easily accomplished by incorporating an old XL 4.0 macro into a
*relative* named formula.

We'll configure this named formula to reference the column just to the
left
of *any cell* containing this formula, though it can be configured to work
on
*any* cell that's relative to the location of the formula containing cell.

Click in B1.
Doesn't matter if it's empty or populated.
From the Menu Bar:
<Insert. <Name <Define

In the "Names In Workbook" box, enter a short name for this formula,
let's use

disp

to denote display.

Then, change whatever's in the "Refers To" box to:

=Get.Cell(6,A1)

Then <OK

With a *relative* reference to A1 (no $ signs), and since you clicked in
B1
at the start, this formula will work on any cell to the immediate left of
*any* cell that contains this formula.

So, say in C1 you entered:
Tyson
In D1 enter
=disp
and you'll get "Tyson" returned.

Now, in C2 enter the formula:
=A1+A2

and in D2 enter:
=disp
And you'll get the formula returned,

=A1+A2

*not* the results of the formula.

Now, to use a Text formula to parse the formula itself:

Enter this formula in any cell to the immediate right of the cell
containing the formula you wish to parse:

=MID(disp,2,FIND("+",disp)-2)

This will return the characters between the equal sign and the first +
sign.

--
HTH,

RD

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

wrote in message
....

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,- Hide quoted text -

- Show quoted text -

Formula to read formula in a cell
 

You're welcome, and appreciate the feed-back.
--

Regards,

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

wrote in message
...
Great! Thanks for the help!!

On Aug 19, 12:34 pm, "RagDyer" wrote:
Forgot the caveat!

XL 4.0 macros can be used in all versions of XL,
*BUT*
In versions before XL02, you should *not* copy them between *workbooks*!
They *can* be copied between sheets within a workbook.

The versions prior to XL02 will crash if an attempt is made to copy
between
*workbooks*.

--
Regards,

RD

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

"RagDyer" wrote in message

...



You can convert a working XL formula into Text that can then be parsed
to
return whatever part of the formula you need.
This is easily accomplished by incorporating an old XL 4.0 macro into a
*relative* named formula.

We'll configure this named formula to reference the column just to the
left
of *any cell* containing this formula, though it can be configured to
work
on
*any* cell that's relative to the location of the formula containing
cell.

Click in B1.
Doesn't matter if it's empty or populated.
From the Menu Bar:
<Insert. <Name <Define

In the "Names In Workbook" box, enter a short name for this formula,
let's use

disp

to denote display.

Then, change whatever's in the "Refers To" box to:

=Get.Cell(6,A1)

Then <OK

With a *relative* reference to A1 (no $ signs), and since you clicked in
B1
at the start, this formula will work on any cell to the immediate left
of
*any* cell that contains this formula.

So, say in C1 you entered:
Tyson
In D1 enter
=disp
and you'll get "Tyson" returned.

Now, in C2 enter the formula:
=A1+A2

and in D2 enter:
=disp
And you'll get the formula returned,

=A1+A2

*not* the results of the formula.

Now, to use a Text formula to parse the formula itself:

Enter this formula in any cell to the immediate right of the cell
containing the formula you wish to parse:

=MID(disp,2,FIND("+",disp)-2)

This will return the characters between the equal sign and the first +
sign.

--
HTH,

RD

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

wrote in message
...

In cell 'A1' I have the formula:

=A2+A3

In B1, is there a way I can write a formula that will grab the first
cell of that formula in 'A1' (in this example the answer would be A2).

Thanks,- Hide quoted text -

- Show quoted text -