# 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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### 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
5
Views
301
Replies
8
Views
199
Replies
3
Views
81
Replies
5
Views
184
Replies
10
Views
702

### Forum statistics

1,144,765
Messages
5,726,164
Members
422,659
Latest member
RGP268 ### 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