Shulou(Shulou.com)11/24 Report--

Original title: "Excel's three most commonly used text functions!" 90% of people don't know. "

Hello, Hi~, I am Tian Tian, who loves to learn from Excel.

It's time for the first phase of [0 basic Excel function] again.

Today we learn three very useful text functions-the LEN function, the LENB function, and the LEFT function.

1. The main function of the LEN function is to calculate the number of characters in the cell.

To make good use of it, you only need to remember one phrase:

Whose character count is calculated?

The case is a Chinese-English comparison table. If you want to calculate the number of characters in each cell, you can use the LEN function.

❶ enters the = LEN function in the B2 cell with "(" parentheses.

❷ We use the mouse to directly select the left cell, put the anti-parenthesis ")", press the enter key, and fill the formula down. As you can see, the results have been calculated.

The complete formula is as follows:

= LEN (A2) it is important to note that when using the LEN function to calculate the number of characters, the space is also counted as a character.

2. LENB function Let's learn another function that is very similar to the LEN function-the LENB function.

The main function of the LENB function is to calculate the number of bytes in a cell.

❶ We enter the = LENB function in the C2 cell, the parameters in parentheses we directly refer to the contents of the [A2] cell, put the anti-parenthesis ")", press the enter key, and fill the formula down.

The complete formula is as follows:

= LENB (A2) you can see that the calculation result of the LENB function is larger than that of the LEN function, and all the Chinese characters in the cell are regarded as two bytes.

In fact, this is because in Excel, Chinese characters will be regarded as two bytes, while English letters will be regarded as one byte. Because of this difference, there will be differences in the calculation results of the LEN function and the LENB function.

If we want to calculate the number of characters in Chinese-English mixed text, we can use these two functions to solve ~

❶ enters the = LENB function in the B2 cell, calculates the number of bytes in the left cell, with ")" anti-parentheses, and then enters a "-" minus sign.

❷ then uses the LEN function to calculate the number of characters in the left cell, put ")" in reverse parentheses, and press enter. Fill in the formula down so that we can calculate the number of Chinese characters in each cell.

The complete formula is as follows:

= LENB (A2)-LEN (A2) 3, the main function of the LEFT function is to return a specific number of characters from the left.

To make good use of it, you only need to remember two tips:

Which cell's characters are returned?

How many characters return to the left-hand side?

How to extract all the Chinese characters in the cell? here, you can use the LEFT function.

Let's directly use the above case to demonstrate. Click the formula cell to enter edit mode and enter the LEFT function after the equal sign. To recall the first phrase, select the cell on the left directly with the mouse and put a comma on it.

In the second sentence, how many characters should you return to the left-hand side?

We just need to package the following function formula as a whole as the second argument of the LEFT function. Put anti-parentheses ")" directly at the back, press enter, and re-drop down to fill the formula. In this way, all the Chinese characters in the text are extracted.

The complete formula is as follows:

How about LEFT (A2)-LEN (A2)? These three text functions are not very practical! Have you learned everything? Go and try it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.