How to Convert Formula Result to Text String in Excel (7 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.

In the dataset, we have sales information for 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.

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 String

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

Steps:

First, select the cells or ranges 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

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.

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.

Thereafter, you will see those dates as text strings. 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.

Still, they are not formatted as text strings. So we select that range and go to the 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 converted to text strings.

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

2. Applying Keyboard Shortcut to Convert Formula Result to Text

You can apply the Copy and 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.

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 the keyboard shortcut.
Later convert these values to text strings like we did in Section 1.

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

3. Applying Excel VBA to Transform Formula Results to Text String

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.

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.

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 in Section 1.
Next, to convert the Sales Price to text strings, go to this link in Section 1 and read the process.

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

4. Using Excel Power Query Editor to Convert Formula Results 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 the 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.

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 are converted to their corresponding values. You will also see that the dates are not properly formatted.

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.

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

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

5. Dragging Formula Results with Mouse to Change 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.

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

Later, place it in its previous position. An option bar will appear. Select Copy Here as Values Only.

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.

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

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

6. Applying TEXT Function to Transform Formula Result

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")

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 the text string.

Use Fill Handle to AutoFill the lower cells.

Similarly, type the following formula in the F5 cell and use the 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.

7. Converting Formula Result to Text String Using 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())

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 the text string.

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

Output of the CONCATENATE function.

Use Fill Handle to AutoFill the lower cells.

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

Similarly, type the following formula in the F5 cell and use the 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.

Practice Section

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

Download Practice Workbook

Converting Formula Result to Text String.xlsm

Conclusion

In the end, we can conclude that you will learn effective methods on how to convert formula result to text string in Excel.

<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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

2. Applying Keyboard Shortcut to Convert Formula Result to Text

3. Applying Excel VBA to Transform Formula Results to Text String

4. Using Excel Power Query Editor to Convert Formula Results to Text

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

6. Applying TEXT Function to Transform Formula Result

7. Converting Formula Result to Text String Using CONCAT or CONCATENATE Function

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply