An example of comprehensive function application in Excel intercepting Chinese

Source: Internet
Author: User

In Excel, you can intercept a variety of characters based on a function, and still do not have a function to intercept Chinese.

Therefore, in order to enable Excel to intercept the Chinese characters in cells, we must use a number of functions to achieve a comprehensive application in order to intercept the corresponding Chinese characters.

Here are some examples that I hope will help you.

As shown in the following table.

Look, the contents of cell A1 are a mixture of Chinese and other English character data, please note that Chinese is either on the left or on the right, and it must be continuous, as shown above, so that it can be intercepted through the integrated function.

I. Interception of characters other than Chinese characters

The following figure.

By formula: =right (A1,len (A1) *2-lenb (A1))

Can be achieved by interception.

Second, the interception of Chinese characters

The following figure.

Directly through the formula: =left (A1,len (A1) *2-lenb (A1))

Can intercept it.

As on the interception, functions are very simple, but difficult to understand, below, to give you a description.

Three, comprehensive function analysis

Left function, we are all aware of the function of intercepting characters on the left-hand side. Refers to the interception from the left. The right function is an intercept function.

The left function Chinese grammar can be read as follows: A1, intercept length, that is, to intercept the string from the first position on the left-hand side, and to intercept the specified length. A1 is the object to be intercepted. The right function is no longer described in the same vein.

Now, let's take a closer look at the integrated functions as above:: =left (A1,len (A1) *2-lenb (A1))

The Chinese syntax for the left is interpreted as left (A1, intercept length), that is, the value of the intercept length parameter is equivalent to Len (A1) *2-lenb (A1).

The Len function is used to find the length of a string. The LenB function is used to find the number of bytes in a string.

Note that, regardless of size, English, Chinese, or other symbols, the length is measured by Len, and each character occupies only one unit. While the LenB function asks for byte size, two units in the Chinese language, not Chinese, only occupy one unit. For an introduction to these two functions, see the article: http://www.dzwebs.net/3164.html

For example: Suppose the contents of the A1 cell are: Volkswagen computer 6789

So, LEN (A1) returns a value of: 9, that is, each character occupies one unit, there are nine, so it returns nine.

LENB (A1), which is used to find the number of bytes in the contents of the A1 cell, returns 14.

Now, our theory is rooted in this idea, the length of the A1 cell is twice times that of Len (A1) *2 minus the number of bytes in the A1 cell.

LEN (A1) *2 is equivalent to the contents of A1, each character by two length.

It follows that the =left (A1,len (A1) *2-lenb (A1) formula intercepts the Chinese on the left.

Of course, the left and right functions, whether in Chinese or English or other symbols, are counted by one length for each word. Only the LenB function calculates one Chinese in two lengths.

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD