How to Convert Text to Formula Using the INDIRECT Function in Excel

This article illustrates how to convert formula in text format to a real formula to make calculations by using the INDIRECT function in Excel. The INDIRECT function helps to make a formula dynamic. We can just change the cell reference value in the text format in a specific cell that is used within the formula without changing it. Let’s dive into the following example to get a clear understanding.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the INDIRECT Function in Excel

We can use the INDIRECT function to get a valid cell reference from a cell value that is stored as a text string.

Syntax:
INDIRECT(ref_text, [a1])

Arguments:
ref_text- This argument is a required one. This is a cell reference, supplied a text that can be either in A1 or R1C1 style.
[a1] – This argument has two values-
If value =TRUE or omitted, the ref_text is in A1 style reference.
and value= FALSE, the ref_text is in R1C1 reference format.


Convert Text to Formula Using the INDIRECT Function in Excel (Step by Step Analysis)

Step 1: Creating a Dataset to Convert Formula to Text in Excel

Let’s say we want to convert a length from meter to feet unit. But the formula which calculates the value is in text format.

Excel Convert Text to Formula Indirect We want to convert the string formula into a real formula that’ll calculate the unit conversion.

Read More: Excel Show Formula as Text in Another Cell (4 Easy Methods)

Step 2: Apply the INDIRECT Function to Convert Text to Formula in Excel

To solve this problem, we’ll use the INDIRECT function in this example. To do that follow the instructions below.

  • In cell F3, put the cell reference that holds the value of length in meter unit i.e., B3.

  • Now in cell G3, write down the following formula.
=3.28*INDIRECT(F3)

Excel Convert Text to Formula Indirect

In the formula, we used TRUE as the value of  [a1] argument that indicates the ref_text argument (B3 in cell F3) is in A1 style reference.

  • Finally, press Enter and the output is 52 ft.

Excel Convert Text to Formula Indirect

Dynamic Formula:
The formula, we used to calculate the conversion, is dynamic. Let’s make some changes-

  • Case 1: If we change the value in B3, the output in G3 will adjust automatically.

Excel Convert Text to Formula Indirect

  • Case 2: In another case, we put a length in the meter unit in cell B4. This time we need to put B4 as the value of cell F3.

The dynamic formula returns the output as 32.8 feet.

Read More: How to Show Formula in Excel Cells Instead of Value (6 Ways)


Things to Remember

  • If we use the ref_text argument from another workbook, we must keep the workbook open to make the INDIRECT function Otherwise, it’ll show #REF! Error.
  • Using the INDIRECT function can cause speed and performance lagging while working with a large dataset.

Conclusion

Now, we know how to convert a text formula into a real formula with the help of Excel’s INDIRECT formula. Hopefully, it would help you to use the method more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo