4 Ways to Convert Excel Formulas or Function Results into Text | Excel Topics
In Excel, you can convert the results of formulas or functions into text for display. This allows you to fix calculation results or format data for easier sharing. This article explains various ways to convert formula or function results into text.
Method 1: Use the CONCATENATE Function or “&” (Recommended)
To combine calculation results with other text, use the CONCATENATE function or the “&” operator.
Example: Combine calculation results with text
Use the following formula to append a space to the value in cell A1:
=A1&""
A | B | C | |
---|---|---|---|
1 | 400 | =A1&"" | =CONCATENATE(A1, "") |
Notes
- This approach allows you to treat calculation results as text and append explanatory notes or units.
- You can combine it with the TEXT function to format the result.
Method 2: Change Cell Format to “Text”
By setting a cell’s format to “Text,” the content is displayed as entered, without being calculated.
Steps
- Select the cell or range of cells you want to treat as text.
- Click the “Home” tab.
- Under “Number,” select “Text” from the dropdown menu.
- Enter the formula or function in the selected cell (e.g., =SUM(A1:A10)).
Results
The entered formula or function is displayed as text, not the calculated result (e.g., “=SUM(A1:A10)”).
Notes
- This method is useful for saving formulas or functions directly as text.
- Cells formatted as “Text” will not calculate the entered formulas.
Method 3: Copy and Paste as Values
The simplest way to convert formulas or functions into text is to copy and paste them as values.
Steps
- Select the cell containing the formula or function.
- Copy the cell (Ctrl + C).
- Select the cell where you want to display the result.
- Under “Home,” choose “Paste” → “Paste Values” (or right-click → “Paste Values”).
Results
The original formula is removed, and only the calculated result remains as text in the cell.
Method 4: Use the TEXT Function
The TEXT function allows you to format numbers or dates and display them as text.
Example: Convert numbers to text
Use the following formula to format the number in cell A1 with commas:
=TEXT(A1, "0.000")
Example: Convert dates to text
Use the following formula to display a date in cell A1 as “yyyy年mm月dd日”:
=TEXT(A1, "yyyy年mm月dd日")
Notes
- The TEXT function allows you to maintain the original numeric or date format while displaying it as text.
- Check Excel’s help or formatting options for more formatting details.
Points to Note
- Using the “Paste Values” method removes formulas from the cell. If you need to preserve the original formula, copy it to another cell first.
- The TEXT function dynamically reflects changes to the original data.
Conclusion
Excel provides several methods to display formula or function results as text. Choose the method that best suits your needs to organize and share your data efficiently. While VBA can also achieve this, it is not covered here for simplicity.