OmniDocHow to convert Excel formula results into text
If you want the formula result to be fixed and no longer change with the source data, you can remove the formula by pasting as a numerical value, F2 plus F9, etc., and explain whether each method will change the number format and whether leading zeros will be lost.
Key points of this article
- Selective paste as numerical value can remove the formula and retain the displayed result, which is suitable for most scenarios
- After pasting, the numbers are still of numerical type. To retain the text format, you need to set the cell format before pasting.
- Press F2 and then F9 to convert only a single cell. It is recommended to use paste when the area is large.
- The conversion is irreversible, keep a copy of the original table before pasting, and check the displayed value, decimal places and leading zeros
In the spreadsheet, many cells are filled with formulas: the amount is calculated by multiplying the unit price by the quantity, the date is spliced by functions, and the number is generated by text functions. These results are usually easy to use, but once you need to send the form to others, paste it into a document, or if the source data is changed but you don’t want the results to change, the formula becomes troublesome. What is needed at this time is to fix the formula results, that is, convert the excel formula into text: retain the content you see now and remove the calculations behind it.
This article is written for people who often do reports, summarization and data sorting. You'll learn several common methods and what each of them will change: whether the formula is removed, whether the numbers can continue to be summed, and whether leading zeros and decimal places are changed. After reading, you can choose one according to your own form and complete the check before delivery.
Two levels of meaning need to be distinguished first. One layer is to remove the formula so that the value will not be recalculated; the other layer is to change the cell data type to text. The two are often needed together, but they are not the same. The following steps will be discussed separately.
How to convert formula results into text? One sentence conclusion
In most cases, a single special paste of the formula area as a numerical value is enough. Additional preparation is only required if you need to preserve text formatting or specify an output style.
| your situation | Recommended practices | reason |
|---|---|---|
| I just want to display the results fixedly, and then also require sum and calculation. | Paste special as numeric value | Remove the formula and the number is still a numerical type |
| To preserve text formatting such as numbers, leading zeros, long numbers | First format the target area as text, then paste it selectively as a numerical value | Change the cell type first, then the pasted content will be stored as text. |
| Only change one or two cells | Double-click to enter editing, press F2 and then F9, and then press Enter | Replace the formula in the cell directly with the calculation result |
| The output style needs to be unified, such as "March 2024" | First use the TEXT function to generate text, then selectively paste it as a numerical value | The format is set in the formula and will not change after pasting. |
Before you start: Confirm these points first
- Confirm whether the original formula needs to be retained. Pasting as a numerical value is irreversible. To restore the original calculation, you can only undo or rewrite the formula. It is recommended to save a copy first, or copy the entire column to a new worksheet as a backup.
- Confirm whether the result still needs to be included in the calculation. Text-type numbers cannot be calculated normally by functions such as SUM. If you need to calculate later, just paste the numerical value and do not set it to text format.
- Check whether the source data will change. The pasted result is the current snapshot. If the form will be handed over to others for continued use, it is recommended to indicate the data date in the file name or header.
- See clearly what the formula returns. If the formula returns an incorrect value (such as #N/A), the error will be fixed after pasting. The source formula should be repaired before conversion.
- Select the target location. Pasting to the original position will overwrite the formula. Pasting to a new column or table is more secure. The original formula can be kept for checking.
Steps to paste formula results as numerical values in Excel
- Select the cell range containing the formula and press Ctrl+C to copy.
- Click the upper left cell of the target location. To replace it in place, click on the upper left corner of the original area.
- Right-click the target location and find the icon with only numerical values in the pop-up paste option (the icon usually displays a numeric style). If not, use Paste Special instead and check the option to paste only numerical values in the dialog box.
- Click any result cell to view the formula bar. If there is no longer an equal sign at the beginning, the formula has been removed.
- If text format is required, select the target column before pasting, change the cell format to text format, and repeat steps 2 to 3. Note: Cells that are already numbers will not automatically turn into text after changing the format and need to be re-entered or pasted.
- Compare the display effect before and after pasting. Pasting as a numerical value will only take away the content, not necessarily the number format. Dates and percentages may become ordinary numbers and need to be reformatted.
- After confirming that it is correct, save it as a new file, and then check a few lines to compare with the original formula column.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is there any formula? | Click on the cell to see if there is an equal sign at the beginning of the edit bar | Not converted in place, copy and paste again as numeric value |
| Is the displayed content consistent? | Compare it row by row with the original formula column, focusing on the decimal places and date | Fill in the corresponding date or percentage format in the cell formatting, or re-paste |
| Whether leading zeros are reserved or not | Is the number such as 001 and the 0 starting with the ID number still there? | Set the target column to text format before pasting; what has been lost should be redone from the source data |
| Can the numbers be calculated? | Use SUM to sum the column to see if the result is reasonable | When the result is abnormal, it may be a text number, which can be returned by dividing the function or multiplying it by 1 and pasting it as a numeric value |
| Whether the error value is fixed | Find # NCMA, # DIV/0! Wait for an error | Reconvert after correcting the source formula |
# Core comparison
Different methods have different effects on the formula, number type and subsequent calculation, so you can choose according to the use.
| Method | Whether or not to remove the formula | Can the result continue to be calculated | Suitable for the scene |
|---|---|---|---|
| Selectively paste as numeric value | Yes | Yes, it's still a numerical value. | Fixed results in large quantities |
| Format the text before pasting | Yes | No direct summation. | Number, long number, leading zero to be retained |
| F2 plus F9, then enter. | Yes, only this cell | Can | Quick modification of individual cells |
| The TEXT function is pasted as a numeric value | Yes | No direct summation. | Text with uniform output style is required. |
# frequently asked questions (FAQ)
1. Can it be restored to a formula after conversion?
Can't recover directly. The original formula has been replaced after being pasted as a numeric value and can only be undone before the file is closed or recovered from the backup copy. Therefore, it is recommended to keep a copy of the original table before conversion.
2. After pasting a number, why does the date become a number?
The numeric paste takes only the content, not necessarily the numeric format, and the date is displayed as a serial number in the normal format. Select these cells and reformat them as dates.
3. Do you also use this method if you want to turn directly typed numbers into text?
No, no, no. Manually entered numbers can be preceded by English single quotation marks, or the cells can be formatted as text before entering. This article is about how the result of the formula is fixed, and the objects and operations of the two are different.
4. There are many formulas and the area is very large, how to deal with it at once?
Select the whole area to copy once and paste it as a numeric value again. You don't have to operate it one by one. Make a backup before pasting, and then check the key columns according to the checklist above to make sure it is correct before delivery.
# Summary
The result of the formula should be fixed as text, and in most cases it can be selectively pasted as a numeric value. It is necessary to retain the number or the leading 00:00 to set the text format. Keeping a backup before conversion and checking the display, format, and calculation results after conversion can avoid unexpected changes after delivery.
