OmniDocHow to use the Excel TEXT function: convert numbers into text in a specified format
Explains how to write the TEXT function in Excel, common format codes and date format codes, explains the difference between it and cell formatting, and gives methods to check the results. It is suitable for readers who need to spell numbers or dates into text.
Key points of this article
- TEXT (value, "format code") returns text, which is suitable for use when splicing sentences and generating numbers. The original value is not affected.
- Cell format only changes the display, while TEXT turns the result into text. The two have different purposes and cannot replace each other.
- For dates, use codes such as yyyy, mm, dd, etc. Note that mm represents minutes in the time part. Wrong writing may result in unexpected results.
- The result cannot be summed directly after the text. When it needs to be involved in the calculation, the original value column should be retained.
In Excel, numbers and dates often need to appear in reports, contract numbers or sentences in a specific style, such as "Total: Amount plus "yuan"", or a date like "2024-03-08". Changing the cell format directly can change the display, but once the results and text are put together, the cell format cannot help. The TEXT function is designed for this scenario: it converts numbers into text according to the format code you write.
This article is written for readers who need to generate fixed-style text, such as people who make reports, organize accounts, or people who often need to write dates into titles. After reading, you should be able to judge when to use TEXT, be able to write commonly used number and date format codes, and know where to start when the result is wrong.
How to use the TEXT function? One sentence conclusion
The basic writing method of the TEXT function is =TEXT(value, "format code"), which will return a piece of text. When you need to generate results such as "amount with thousandths", "number with fixed digits", "Chinese date", you can directly use TEXT; when you just want the numbers to display better and still participate in calculations, it is more appropriate to set the cell format.
| your situation | Recommended practices | reason |
|---|---|---|
| You need to spell the amount and "yuan" into one sentence | Use TEXT plus & to concatenate | What is spliced must be text. TEXT can directly output the formatted text. |
| I just want the amount in the table to show two decimal places | Format cells | The numerical value remains unchanged, and subsequent summation and sorting will not be affected. |
| The number needs to be filled with digits, such as 007 | Use TEXT(number, "000") | Format code 0 will be padded with zeros in front, and the result can be used directly for splicing. |
| The date should be written as "March 8, 2024" | Use TEXT to add date format code | Date codes can be combined into Chinese styles in your order |
| As a result, we have to continue to do addition and subtraction operations. | Keep the original value and create a new TEXT column for display | The result of TEXT is text. If you directly participate in the operation, you will make an error or get an incorrect value. |
Before you start: Confirm these points first
Before writing the formula, confirm the following things to avoid most rework.
- Confirm whether the source data is numeric or text. TEXT only works on numeric values. If there is a small green triangle in the upper left corner of the cell, or the number is aligned to the left, it is most likely a text number and needs to be converted to a numeric value first, otherwise the formatting code will not work as expected.
- Confirm whether you want "Display" or "Splicing". To format only for display, it is more reliable to use cell format; TEXT is only needed to put the results into a sentence or a number.
- Understand what the format codes mean. 0 indicates the digit that must be displayed, # indicates that it will be displayed only if there is a value, and the question mark indicates that spaces are used to occupy the place. In the date, m may be either the month or the minute. When it comes after h or ss, it means the minute.
- Be aware of your Excel locale. The display method of date and week will change with the system language and Excel version. The same format code may display different text on different computers. It is recommended to try it on the other party's computer before sharing the file.
- Keep original data columns. The result of TEXT is text. It is recommended to keep the original value in one column and the formatted result in the next column, so that when recalculation is needed, the number does not need to be deduced from the text.
Steps to use the TEXT function to format numbers in Excel
The following takes "Convert the amount in column B into text with thousandths and two decimal places, and spell 'yuan'" as an example to demonstrate the complete operation.
- Enter the equal sign and function name in a blank cell (for example, C2) and write it as =TEXT(B2,"#,##0.00"), which means converting the value of B2 into text in the format of thousandths and two decimal places.
- Press Enter to confirm and check whether C2 is displayed with thousandths and two decimal places. If an error value is displayed, return to step 1 to check whether the format code is written in English double quotes.
- If you need to splice text, use the & symbol to connect it in the formula, for example ="Total amount:"&TEXT(B2,"#,##0.00")&"Yuan", the result will be "Total amount:" followed by the formatted amount, plus the word "Yuan".
- When formatting the date, change it to a date code, for example =TEXT(A2,"yyyy year mm month dd day"), A2 must be a real date value, not text that looks like a date.
- When you need to add zero numbers, use =TEXT(7,"000"), and the result is "007", which is suitable for generating order numbers or serial number prefixes.
- If there is a percent sign at the end of the percentage format code, Excel will multiply the value by 100 before displaying it, so the source data should be a decimal, not an integer that has been multiplied by 100. Before writing the formula, confirm the decimal position of the source data to avoid the result being a hundred times larger than expected.
- After confirming that the formula is correct, drag the fill handle downward to apply the same formula to the entire column.
- Go back and check the result in column C, compare it row by row with the original value in column B, and confirm that the format, decimal places, and date order are correct.
Check each item after completion
After filling in the formula, don't rush to copy the results. Check it first according to the list below.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the result is displayed as text | Select the result cell to see if the edit bar looks formatted and aligned to the left | If the result is calculated as a number, it means that there is an error in splicing or conversion, go back to formula check & and quotation marks |
| Is the decimal place correct? | Compare the original value to see if the 0.00 type code retains two digits. | If the number of digits is incorrect, modify the number of 0 or # after the decimal point in the format code |
| Date order and text | Check whether yyyy, mm and dd are arranged according to your requirements, and whether the Chinese characters are sandwiched in the correct position. | Check if m is mistaken for minutes, use mm or add text before and after if necessary |
| Error value | See if the cell appears # VALUE! Hints like that. | Usually the source data is miswritten in text or format code, so the source data is converted to numeric value and then rewritten |
| Whether it is consistent with the original data | Extract a few lines to manually check with the original value | When inconsistencies are found, check whether the referenced cells are correct to avoid misplacement of references in the whole column. |
After the completion of the check, if the result will be used by others, it is recommended to copy it as a value and save a copy to avoid subsequent changes to the original data.
# Core comparison
There are two common ways to make numbers "good-looking": formatting cells and using the TEXT function. The difference between the two is as follows.
| Contrast item | Format cells | TEXT function |
|---|---|---|
| Type of result | It's still a numerical value. | Return text |
| Can you continue to calculate | Can be summed and sorted directly | Can not directly participate in the numerical calculation |
| Can it be spliced with the text? | Cannot be spliced directly. | You can use & to combine with text |
| Applicable scenario | Table display, report beautification | Generate sentences, numbers, titles |
| Whether the original value has changed | Do not change | Does not change, but generates a new text result |
In general, numbers in tables are processed in cell format first; TEXT is used only when you need to put numbers in text.
# frequently asked questions (FAQ)
1. Why can't the result of TEXT function be summed?
TEXT returns text, and even if it looks like a number, Excel does not accumulate it as a numeric value. When a sum is required, the original numerical column should be summed, and the TEXT column is used for display only.
2. Why is the date displayed as a number after using TEXT?
This is usually because the source cell itself is not a date value, but a number or text. Verify that the source data is the real date before using yyyy, mm, dd, and so on.
3. What's the difference between mm and m?
In the date format, m and mm both represent months, and mm is completed into double digits, such as 03. But if it follows h or ss, it means minutes. Pay attention to this when writing the time format.
4. What day of the week is displayed in English or other languages?
The display of the week is related to the language setting of Excel, and the same format code may output different text in different locales. If you need to be fixed in Chinese, you can directly use Chinese characters plus IF or CHOOSE in the formula, or confirm the language setting on the target computer before using it.
5. Do the results processed with TEXT need to be checked?
Need. The results of the TEXT itself are certain, but if the format code or source data is miswritten, the results will deviate from expectations. Comparing the sampling of key data with the original value is a common practice to ensure the accuracy of the report.
# Summary
The core of the TEXT function is to use format codes to turn numbers or dates into text of a specified style, which is suitable for stitching sentences, generating numbers, and arranging titles. Before writing the formula, confirm that the source data is a numerical value, then select the right format code, and check the results item by item.
