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

Our sample dataset contains sales information for a grocery store. We used an arithmetic formula and the TODAY and SUM functions in it. We are going to remove the formulas and keep the results as a text string.

Method 1 – Using the Copy and Paste Feature to Convert a Formula Result to a Text String

Steps:

• Select the cells or ranges that contain formulas.
• Press Ctrl + C.

• 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 will paste over the formula with its result.

• To convert these values as text strings, you can simply change their format to text from the number format. However, it won’t provide the exact same look.
• Select the cells that contain value and then go to Home, then choose Find & Select and pick Replace.

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

• A notification box will show up saying how many replacements have been done. Click OK.

• You will see those dates as left-aligned text strings.

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

• Select that range and go to the Number Format Group.
• Choose the Text format.

• The values are left-aligned so they are text strings.

Method 2 – Applying a Keyboard Shortcut to Convert a Formula Result to Text

Steps:

• Select the range of cells that contain formulas and press Ctrl + C or Ctrl + Insert.

• Press Shift + F10. If you are using a laptop, you may need to press Shift + Fn + F10.
• You will get the Context Menu.

• Press V. The results of the formulas are converted to values.

• Convert these dates to text strings like in Method 1.
• Convert the Sales Price and Total Sales formula results to values by using the keyboard shortcut.
• Convert these values to text strings like we did in Method 1.

Method 3 – Applying Excel VBA to Transform Formula Results to Text Strings

Steps:

• Go to Developer and pick Visual Basic.

• The VBA editor will open up. Select Insert and choose Module.

• Insert 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

• Run the code.
• 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 Method 1.
• To convert the Sales Price to text strings, follow Method 1.

Read More: Excel VBA: Convert Formula to Value Automatically 

Method 4 – Using the Excel Power Query Editor to Convert Formula Results to Text

Steps: 

• Select the whole dataset.
• Go to Data and choose From Table/Range
• A dialog box will show up. Select My table has headers.
• Click OK.

• You will see your data of formula results in a Power Query Editor.

• Select Close & Load from the Home tab.

• This will shift the 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.
• Select a Date Format.

• Here are the converted dates.

• To convert the Dates to text strings, follow this link in Method 1.
• To convert the Sales Price to text strings, follow Method 1.

Method 5 – Dragging Formula Results with the Mouse to Change Them to Text

Steps:

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

• Hold the right-click button and move the range anywhere.

• Place it in its previous position.
• An option bar will appear. Select Copy Here as Values Only.

• This will convert the formula results to values.

• Convert the Sales Price and Total Sales formula results to values.

• Convert the values to text by following Method 1.

Method 6 – Applying the TEXT Function to Transform Formula Results

Steps:

• Use the following formula in cell B5:

• Press Enter and you will see the Date in B5 as a text string.

• Use the Fill Handle to AutoFill to the lower cells.

• Use the following formula in the F5 cell and use the Fill Handle to Autofill the Sales Price column.

• You can then remove the formula by following any of the methods above.

Read More:How to Convert Formula to Value Automatically in Excel

Method 7 – Converting Formula Results to Text Strings Using the CONCAT or CONCATENATE Function

Steps:

• Use the following formula in cell B5:

Alternatively, use the CONCATENATE function.

• These functions implicitly convert its arguments to strings and return text strings as a result.
• Press Enter.

• Output of the CONCATENATE function.

• Use the Fill Handle to AutoFill the column.

• To convert the dates to proper format, follow Method 4.
• Use the following formula in the F5 cell and use the Fill Handle to Autofill the Sales Price column:

• The CONCATENATE function will give you the same result.
• 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.

Some Excel versions have only one option between CONCAT and CONCATENATE, so type the formula elsewhere and see which result is suggested.

Practice Section

We provided a practice sheet in the download file so you can test these methods.

Download the Practice Workbook

Related Articles

• Convert Formula to Value in Multiple Cells in Excel
• How to Stop Formula to Convert into Value Automatically in Excel
• How to Return Value of Cell Not Formula in Excel
• Putting Result of a Formula in Another Cell in Excel

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