# How do you refer to the text of a formula?

#### kwkeller

##### New Member
Hello list,
How would I refer to the text of a formula in another formula? For ex:

If I have the following formula

=SUBTOTAL(3,D2:D10) which equals 9

I would like to extract the range D2:D10 out and use it in another formula. However all that I'm geting is the result of the formula (9) not the "text" of the formula.

How do you do this?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### plettieri

##### Well-known Member
Is this what you mean...you can name the range and then use that range name in wherever you want on the spread sheet?

Insert | name | define ..gets you to name the range..

pll

#### Joe Was

##### MrExcel MVP
Combine both text functions and value functions into a string. For Sum(B1:D1) use:

="SUM("&B1&"+"&C1&"+"&D1&")="&SUM(B1:D1)

This is what you will see in your cell:
SUM(10+10+10)=30
if B1,C1&D1 each = 10.

And

="SUM(B1="&B1&" + C1="&C1&" + D1="&D1&")="&SUM(B1:D1)

will give: SUM(B1=10 + C1=10 + D1=10)=30
in the cell containing the mixed text formula above.

Hope this helps. JSW

#### Asala42

##### Well-known Member
If I have the following formula

=SUBTOTAL(3,D2:D10) which equals 9

I would like to extract the range D2:D10 out and use it in another formula. However all that I'm geting is the result of the formula (9) not the "text" of the formula.

Here 's a way if you're not opposed to a UDF approach. To Start, here's a little UDF that will translate a formula to a string of text:

Code:
``````Function FormulaText(x As Range) As String
FormulaText = x.Formula
End Function``````

In another cell, type the formula:
=FormulaText(D12) (assuming your subtotal formula is in D12)
This should kick out the string: "=SUBTOTAL(3,D2:D10)".

Now you're free to wrap a Find/Mid Combo to pull the D2:D10 part of the string. example:

=MID(formulatext(D12),FIND(",",formulatext(D12))+1,6) will pull "D2:D10" as a string. You could then use INDIRECT with that string in other formulas.

Hope that helps somewhat

Replies
6
Views
976
Replies
12
Views
760
Replies
3
Views
973
Replies
5
Views
506
Replies
4
Views
215

1,181,530
Messages
5,930,428
Members
436,738
Latest member
JFry

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back