How to Convert Formula Result to Text String in Excel (7 Easy Ways)

The article will show you how to convert formula result to text string in Excel. Sometimes it’s good for us to use the formula results as values in Excel, because we don’t need formulas to tag a price for a product or use a date every time. Moreover, it may be annoying to you to copy a single data that contains a formula because whenever you copy that, you cannot normally paste it without the formula. Which may cause you unnecessary errors. So converting formula result to text string or values is sometimes important after their execution.


Download Practice Workbook


7 Ways to Convert Formula Result to Text String in Excel

In the dataset, we have sales information of a grocery store for today. We used an arithmetic formula, TODAY and SUM functions in it. We are going to remove the formula and keep the results of the formula as a text string. I just showed you the basic formulas that we used in the following figure.

excel convert formula result to text string intro

Keep that in mind, the cells that contain formulas are not formatted. So if the formula output is a number, it will be on the right side of a cell. And if it becomes a text string then it will hold the left side of the cell.


1. Using Excel Copy & Paste Feature to Convert Formula Result to Text 

We can easily convert formula results to text string by using the Copy & Paste feature of Excel. Let’s go through the procedure below.

Steps:

  • First, select the cells or range that contain formulas.
  • Next, press CTRL+C.

  • Later, right-click on any of the selected cells and select the Paste Option Paste Values’. You can see this option in both Paste Options: and Paste Special

excel convert formula result to text string using copy and paste

This operation will store the formula results of dates as values and terminate the formula.

  • To convert these values as text strings, you can simply change their format to text from the number format. But as these values are dates, this conversion won’t be convenient. Instead of formatting them as text from the Number Format, we make them text using the Find & Replace For that reason, select the cells that contain value and then go to Home >> Find & Select >> Replace.

excel convert formula result to text string using copy and paste

  • In the Find and Replace dialog box, type 1 and ‘1 in Find what and Replace with sections respectively.
  • Click Replace All.

  • After that, a warning box will show up saying how many replacements have been done. Just click OK.

excel convert formula result to text string using copy and paste

  • Thereafter, you will see those dates as text string. Notice that they hold the left side of the cells.

  • There is another column that contains formulas in my dataset. I converted the results to values in the same way.

excel convert formula result to text string using copy and paste

  • Still, they are not formatted as text strings. So we select that range and go to Number Format Group.
  • After that, we chose the Text format.

  • Later, you will see the values as text strings. Notice that, the values shift to the left in the cells, which you can say is proof of them being are converted to text strings.

excel convert formula result to text string using copy and paste

Thus you can convert the formula results to text strings by using the Copy & Paste feature of Excel.

Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting


2. Applying Keyboard Shortcut to Convert  Formula Result to Text in Excel

You can imply the Copy & Paste feature by using the keyboard shortcut too. Let’s go through the process below.

Steps:

  • First, select the range of cells that contain formulas and press CTRL + C or CTRL+INSERT.

  • Next, press SHIFT+F10. If you are using a laptop, you may need to press SHIFT+FN+F10.
  • You will see the Context Menu bar will appear.

excel convert formula result to text string using keyboard shortcut

  • Thereafter, just press V. You will see the results of the formulas now converted to values.

  • Convert these dates to text strings like we did in Section 1.
  • After that, convert the Sales Price and Total Sales formula results to values by using keyboard shortcut.
  • Later convert these values to text strings like we did in Section 1.

excel convert formula result to text string using keyboard shortcut

Thus you can convert the formula results to text strings by using the keyboard shortcut.

Read More: Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)


3. Using VBA to Convert  Formula Result to Text in Excel

You can also use a simple VBA code to convert formula results to values and then convert them to text strings. Let’s follow the instructions below.

Steps:

  • First, go to Developer >> Visual Basic.

  • The VBA editor will open up. Select Insert >> Module.

excel convert formula result to text string using VBA

  • After that, type the following code in the VBA Module.
Sub ConvertToTextString()
Dim Range_Value As Range
Dim Cell_Value As Range
Set Range_Value = Selection
For Each Cell_Value In Range_Value
If Cell_Value.HasFormula Then
Cell_Value.Formula = Cell_Value.Value
End If
Next Cell_Value
End Sub

Code Explanation

  • First, we name the Sub Procedure ConvertToTextString.
  • Next, we declare Range_Value and Cell_Value as Range.
  • Later, we set Range_Value to Selection property.
  • After that, we used a For Loop to convert the Cell Formulas to Cell Values.
  • Finally, we run the code.
  • Next, go back to your sheet, select the cells that contain formulas and Run the Macro.

excel convert formula result to text string using VBA

  • This operation will convert the formula results to values, which means the formulas will disappear and just the values will remain.

  • To convert the Dates to text strings, follow this link of Section 1.
  • Next, to convert the Sales Price to text strings, go to this link of Section 1 and read the process.

excel convert formula result to text string

Thus you can convert the formula results to text strings by using the VBA.

Read More: Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)


4. Implementing Excel Power Query Editor to Convert Formula Result to Text

Using Excel Power Query Editor can be a vital method to convert formula results to text strings. Let’s go through the process below.

Steps: 

  • First, select the whole range of dataset.
  • After that, go to Data >> From Table/Range
  • A dialog box will show up. Make sure you select My table has headers.
  • After that, Click OK.

  • Next, you will see your data of formula results in a Power Query Editor.

excel convert formula result to text string using power query editor

  • After that, select Close & Load from the Home Tab.

  • This will shift this data to a new sheet as a table. This table does not contain any formula, meaning all the formula results converted to their corresponding values. You will also see that the Dates are not properly formatted.

excel convert formula result to text string

  • To format the Dates properly, select them and go to the Number Group.
  • After that, select a Date Format.

  • This will serve you the Dates in an appropriate format.

excel convert formula result to text string

  • After that, to convert the Dates to text strings, follow this link of Section 1.
  • Next, to convert the Sales Price to text strings, go to this link of Section 1 and read the process.

Thus you can convert the formula results to text strings by using the Power Query Editor.

Read More: Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)


5. Dragging Formula Results with Mouse to Convert Them to Text

Another simple way to convert formula results to text strings is to use the right-click dragging cells or range feature. Please go through the procedure below.

Steps:

  • First, select a range that contains formulas and place your cursor on any edge of the selected cells so that the marked icon

excel convert formula result to text string

  • After that, hold the right-click button on it and move the range anywhere.

  • Later, place it to it’s previous position. An option bar will appear. Select Copy Here as Values Only.

excel convert formula result to text string

This operation will convert the formula results to values, which means the formulas will disappear and just the values will remain.

  • Similarly, convert the Sales Price and Total Sales formula results to values.

excel convert formula result to text string

  • To convert the Dates to text strings, follow this link of Section 1.
  • Next, to convert the Sales Price to text strings, go to this link of Section 1 and read the process.

Thus, you can convert the Excel formula result to text string by dragging them with the right-click drag feature.


6. Applying TEXT Function

If you don’t want to use commands, you can use the TEXT Function to convert the formula results to text strings. Let’s go through the process below.

Steps:

  • First, type the following formula in cell B5. Just add the TEXT Function.

=TEXT(TODAY(),"dd-mm-yy")

excel convert formula result to text string using TEXT function

The TEXT Function converts the formula results of the TODAY Function to text strings and also its format.

  • Press ENTER and you will see the date in B5 shifts to the left meaning it is converted to text string.

excel convert formula result to text string

  • Similarly, type the following formula in F5 cell and use Fill Handle to Autofill the lower cells in the Sales Price

=TEXT(D5*(1-E5),"0.00")

You can also see that the Total Sales become 0 dollars because the sales prices are in text format. The SUM function can calculate the summation of values which are in text form.

Thus, you can convert formula results to text strings with the help of the TEXT Function.

Read More: How to Convert Formulas to Values in Excel (8 Quick Methods)


7. Applying CONCAT or CONCATENATE Function

You can also use the CONCAT or CONCATENATE Function to convert the formula results to text strings. Let’s go through the process below.

Steps:

  • First, type the following formula in cell B5. Just add the CONCAT or CONCATENATE Function.

=CONCAT(TODAY())

excel convert formula result to text string

The following formula uses the CONCATENATE function.

=CONCATENATE(TODAY())

Normally, the CONCAT or CONCATENATE Function adds multiple strings together and stores them as strings. As we just used formula results in CONCAT or CONCATENATE, we will see only one value converted to text string.

  • Press ENTER and you will see the date in B5 shifts to the left meaning it is converted to text string. Also, you can see that the date is not properly formatted.

excel convert formula result to text string

Output of the CONCATENATE function.

  • Use Fill Handle to AutoFill the lower cells.

excel convert formula result to text string using CONCAT or CONCATENATE function

To convert the dates to proper format, kindly follow this link of Section 4.

  • Similarly, type the following formula in F5 cell and use Fill Handle to Autofill the lower cells in the Sales Price

=CONCAT(D5*(1-E5))

The CONCATENATE function will give you the same result.

You can also see that the Total Sales become 0 dollars because the sales prices are in text format. The SUM function can calculate the summation of values which are in text form.

Thus, you can convert formula results to text strings in Excel with the help of the CONCAT or CONCATENATE Function.

Read More: How to Convert Formula to Value Automatically in Excel (6 Effective Ways)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

excel convert formula result to text string


Conclusion

In the end, we can conclude that you will learn effective methods on how to convert formula result to text string in Excel. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo