Extracting elements of formula (arguments) into another cell

[Wigs]

Hi,

If I have a formula in a cell, is there any means of extracting a specific element of that formula into another cell?

For example, say cell A1 contains:

=SUMPRODUCT(--(No_Expenses=C$6),--(Exp_Cat=$A14),Exp_Total)

If I want cell B1 to contain "C$6" (or maybe even the whole of the first argument) is there a way of doing this?

Alternatively, is there a way of converting the formula into just a text string (i.e. effectively removing the "=" from the start of the formula) so that I can then manipulate just the text?

Don't want to use VB - hoping Excel functions will suffice..........

Many thanks for any assistance,

Martin.

 

[pgc01]

Thank you Glenn. You are right, it's a typo.

Like you wrote, the correct text would be:

(2): =Formula&T(N(A1))

 

[Wigs]

Hi all,

Thanks for all the replies - have been out of the office for a couple of days, so will give them a whirl and let you know how I get on.

Thanks once again,

Martni.

 

[Wigs]

You guys really do save me a lot of time and brainache!!

Using the defined name seems to work and I can drag handle around to fill in a table, so I now have my formulas extracted to a number of cells, which is great. As they are all the same length, I can now easily use a couple of LEFTs and RIGHTS to suck out the elements I wish.

However.....my LEFT and RIGHT manipulation feels a little clumsy and wouldn't work if I had a formula that was a different length.

Therefore, is there any clever way of saying "get me the text between two characters"?

So, for example, I now have 3 cells (say A10:A12) that contain the following:

=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A13),Expenses!$E$6:$E$991)
=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A14),Expenses!$E$6:$E$991)
=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A15),Expenses!$E$6:$E$991)

Is there a clever text function that will extract the data between the 2 commas, e.g. --(Expenses!$D$6:$D$991=$A13) assuming that the string between the 2 commas may differ in length?

Or should I really be doing all of this in VB (which I'm not adverse to.......just feel like these functions should already exist!)?

Thanks in advance once again,

Martin.

 

[T. Valko]

You guys really do save me a lot of time and brainache!!

Using the defined name seems to work and I can drag handle around to fill in a table, so I now have my formulas extracted to a number of cells, which is great. As they are all the same length, I can now easily use a couple of LEFTs and RIGHTS to suck out the elements I wish.

However.....my LEFT and RIGHT manipulation feels a little clumsy and wouldn't work if I had a formula that was a different length.

Therefore, is there any clever way of saying "get me the text between two characters"?

So, for example, I now have 3 cells (say A10:A12) that contain the following:

=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A13),Expenses!$E$6:$E$991)
=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A14),Expenses!$E$6:$E$991)
=SUMPRODUCT(--(Expenses!$B$6:$B$991=AI$6),--(Expenses!$D$6:$D$991=$A15),Expenses!$E$6:$E$991)

Is there a clever text function that will extract the data between the 2 commas, e.g. --(Expenses!$D$6:$D$991=$A13) assuming that the string between the 2 commas may differ in length?

Or should I really be doing all of this in VB (which I'm not adverse to.......just feel like these functions should already exist!)?

Thanks in advance once again,

Martin.

If you're proficient with VBA then using VBA would probably be easier for you. I'm pretty much a "hacker" when it comes to VBA but, as bad as I am with VBA, I'm that good with formulas!

This will extract the substring between the 2 commas...

=MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-1-FIND(",",A2))

Assumes that there will always be at least 2 commas in the string.

 

[Wigs]

Legend!

My VBA and Excel functions are both OK for the day to day but neither are good enough to do most of the complex things I want to do - hence why this message board is so invaluable!!

One question I did have (having gone through the various "help" (and I use the word loosely....) topics) on the CELL function is why does the value "6" work in the GET.CELL call? I can't find any argument definitions other than text based "INFO_TYPE".

Cheers,

Martin.

 

[GlennUK]

The 6 in GET.CELL is the argument for getting the formula of the cell. Look here for the full list of possible return types:

http://www.mrexcel.com/forum/showthread.php?t=20611

 

[Wigs]

Thanks Glenn - that makes sense now.

Cheers,

Martin.