The function of the TEXT function in Excel is to format numbers into a specified text format. 1. Its basic usage is =TEXT (value, "format code"), which is used to convert numerical values into custom display forms; 2. Common uses include converting numbers into fixed format strings (such as zero-compensation, percentage) and maintaining format when splicing text; 3. In the format code, 0 means a must-have digit, # means optional digits, % means multiplying by 100 and adding a percent sign; 4. Note that the return result is the text type and cannot directly participate in the operation; 5. It can be combined with TODAY, IF and other functions to achieve more flexible data display effects.

In Excel, the TEXT function is a very practical tool, especially when you need to format numbers into specific text formats. Instead of doing complex calculations like other functions, it focuses on "how to display numbers". Simply put, its purpose is to turn numbers into what you want them to be.

Basic usage: TEXT (numerical value, format code)

The basic structure of the TEXT function is as follows:

 =TEXT(value, "format code")

The first parameter is the value or cell reference you want to convert, and the second parameter is the format you want it to display. For example, if you want to display a date as "October 5, 2024", or display the number 0.5 as "50%", you can all use the TEXT function to implement it.

For example:

• Cell A1 is 2024/10/5
• The result returned by formula =TEXT(A1,"yyyy年mm月dd日") is 2024年10月05日

Common use 1: Convert numbers into strings in fixed format

Sometimes we need to turn numbers into a unified format, such as ID number, phone number, product number, etc. At this time, directly using the TEXT function can save a lot of manual operations.

Common format examples:

• Numbers complement zero to 4 digits: =TEXT(123,"0000") → 0123
• Show two decimal places: =TEXT(3.1,"0.00") → 3.10
• Percentage with two decimal places: =TEXT(0.5,"0.00%") → 50.00%

Placeholder description in format code:

• 0 means that one number must be displayed, if not, zero will be added
• # indicates the number of optional digits, if not, it will not be displayed
• . the decimal point
• % is automatically multiplied by 100 and added a percent sign

Common use 2: Keep format when splicing text and date/number

When you want to spell some text and numbers together, but don't want their formatting to be messed up, the TEXT function comes in handy.

for example:

• ="訂單編號：" & TEXT(12345, "00000") →訂單編號：012345
• ="截止時間：" & TEXT(TODAY(), "yyyy-mm-dd") →截止時間：2024-10-05

The result of this spelling looks neat and is more suitable for export or display.

Note: The TEXT function returns text

This is very important. Although you can see numbers, they are essentially text types and cannot participate in mathematical operations. If you want to use this data for addition, subtraction, multiplication and division later, you must be careful not to use the TEXT function to process it.

for example:

• =TEXT(100,"0") 1 This formula will report an error (because the previous result is text)
• The correct way to do it should be to calculate first and then convert the format: =TEXT(100 1,"0")

Tips: Use better with other functions

The TEXT function is often used in conjunction with other functions, such as TODAY(), NOW(), A1 reference, etc.

Let me give you a few common combinations:

• Show current date: =TEXT(NOW(),"yyyy年m月d日hh:mm")
• Splicing score level: =IF(A1>=60,"及格","不及格") & " - " & TEXT(A1,"00分")
• Different formats are displayed according to the conditions: =TEXT(A1, IF(B1="人民幣","#,##0元", "#,##0 USD"))

Basically that's it. The TEXT function is not complicated, but if used properly, it can make your data display more professional and avoid many problems of format confusion. The key is to remember how to write the format code, and when to use it and when not to use it.

The above is the detailed content of How to use the TEXT function in Excel. For more information, please follow other related articles on the PHP Chinese website!