How to use Excel's TEXT function?

This article brings you relevant knowledge about excel, which mainly introduces related issues about the text function. The TEXT function is one of the most frequently used text functions. Let’s talk about it together. Let’s take a look at the common uses of this function. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

The TEXT function is one of the most frequently used text functions. She has only two Parameters, parameter 1 is the number to be processed, parameter 2 is used to specify the format code, which is basically the same as most of the codes in cell number format.

Next, let’s take a look at the common uses of the TEXT function:

1. Simple conditional judgment

The following figure shows part of the employee evaluation form of a certain unit. It needs to be evaluated based on the assessment scores. A score of 85 or above is considered good, a score of 76 to 85 is considered qualified, and a score of 75 or less is considered unqualified.

Enter the following formula in cell C2 and copy it down.

=TEXT(B2,"[>85]Good;[>75]Qualified; Unqualified")

The formula used is Three-section format code containing custom conditions. The usage of format codes is almost exactly the same as custom formats.

2. Connect formatted content

As shown in the figure below, you need to connect the name in column A and the date of birth in column B.

Enter the following formula in cell C2 and copy it down.

=A2&TEXT(B2," y year m month d day")

First use the TEXT function to change the date in column B into a specific style string, and then concatenate it with the name in column A, it becomes the final required style.

3. Convert date format

As shown in the figure below, the date format in column B needs to be converted into the month in Chinese format.

Enter the following formula in cell C2 and copy it down.

=TEXT(B2,"[DBnum1]m month")

The format code "m" is used to extract the month in cell B2, and then Use the format code [DBnum1] to convert it to Chinese lowercase number format.

4. Rounded interval hours

Calculate the hours interval between two times, and discard the part that is less than one hour.

Enter the following formula in cell D2 and copy it down:

=TEXT(C2-B2,"[h]")

Use [h] in the format code , represents the hour after rounding.

5. Extract the date of birth

As shown in the figure below, the date of birth must be extracted based on the ID number in column B.

Enter the following formula in cell C2 and copy it down:

=--TEXT(MID(B2,7,8),"0-00-00")

The MID function is used to extract a specific number of strings starting from a specified position in the string.

MID(B2,7,8) starts from the 7th digit of cell B2 and extracts 8 digits. The result is:

20020718

Then use the TEXT function , change this string into the pattern of "0-00-00", and the result is "2002-07-18".

At this time, the date already looks like it, but it is still text type, so two negative signs are added, which is to calculate the negative number of the negative number. After such a toss, it becomes a real date. Sequenced.

6. Simplified formula judgment

As shown in the figure below, the difference in changes must be judged based on the two years of data in columns B~C.

Enter the following formula in cell D2:

=TEXT(C2-B2,"0 yuan more than the previous year; 0 yuan less than the previous year; the same as the previous year")

The second parameter of the TEXT function uses "0 yuan more than the previous year; 0 yuan less than the previous year; the same as the previous year", which means:

If C2-B2 If the result is greater than 0, it shows "n yuan more than the previous year."

If the result of C2-B2 is less than 0, it will display "n yuan less than the previous year".

If the result of C2-B2 is equal to 0, it will display "Same as the previous year".

The 0 in the TEXT function format code has a special meaning, usually indicating the value of the first parameter itself.

7. Quickly extract characters

As shown in the figure below, all numbers before the minus sign must be extracted based on the numbers in column A.

Enter the following formula in cell B2 and copy it down:

=TEXT(,"[$"&A2&"]")

If you think this formula is still too long, you can try the following formula:

=IMREAL(A2&"i")

Related learning recommendations: excel tutorial

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