As a data developer, in the process of work, although the database is used in most cases, the operation of Excel is unavoidable.The text() function has powerful functions. I will share more scenes encountered in my work below, enjoy~~

I. Meaning

Excel's text function is to format the content of the selected cell through the format code, and then display it in different display methods. It is a function that converts the content into text

Second, grammar

=TEXT (value, format code)
Parameter definition:
The first parameter value is the selected cell, which is the content that you want to format as text. The content of this cell can be numeric or characterString etc.
The second parameter is the target format you want to format.

III. Common usage scenarios

1, the value display is filled with 0

Format: =TEXT(cell,"000000")
Interpretation: Fill the content of the cell with 0 in front of the six digits, for example, 100 will be formatted as 000100

Extension: If you want to display two digits after the decimal point, the less than 2 digits are filled with 0s, and more than 2 digits are filled with 0.2-digit rounding, the formula is: =TEXT(cell, "00.00").For example, 21 is formatted as 21.00; 15.135 is formatted as 15.14.

2, extract date of birth based on ID number

Format: =TEXT(MID(cell,7,8),"00-00-00")

3, standard three-segment format for phone numbers

Format: =TEXT(cell,"000-0000-0000")

4, convert a numeric date to a specific date format

(1) 00/00/0000

Format: =TEXT(cell, "00/00/0000")

(2) 0000-00-00

Format: =TEXT(cell, "0000-00-00")

(3) full name of the day of the week

Format: =TEXT(TEXT(cell,"0000-00-00"),"aaaa")

(4) Abbreviated day of the week

Format: =TEXT(TEXT(cell,"0000-00-00"),"aaa")

(5) Year of extraction

Format: =TEXT(TEXT(cell, "0000-00-00"), "yyyy")

(6) Month of withdrawal

Format: =TEXT(TEXT(cell,"0000-00-00"),"mm")

(7) Pickup Day

Format: =TEXT(TEXT(cell,"0000-00-00"),"dd")

5, conditional section judgment

Condition section, you can choose the default, two, three, four sections, or can be customized

(1) Default condition section

Three sections are used more often, of course, you can also modify it to other sections according to your needs.
Format: [>0];[<0];[=0]

(2) Custom Condition Section

Format: [Condition 1];[Condition 2];[Other parts that do not meet the condition];[Text]

The above is the common way we use the text function in Excel, I am Meow~~, I hope everyone can communicate and learn together!