From rookie to become a master of the Find function of Excel

Eighth trick: blind men feeling an elephant (Find function)

This trick is used on the original data string in a position to determine its location. Locating because the move, always starting from the specified location and returns the first matching string found in position, regardless of whether there followed a string of matches, a bit like the blind men feeling an elephant, which he said touched which hence the name "blind elephant."

Syntax

FIND (find_text, within_text, start_num)

Find_text is to find the text.

Within_text to find text that contains the text.

Start_num start looking for the specified character. within_text the first character is numbered 1 characters. If you ignore the start_num, it is assumed to be 1.

Note:

Use start_num to skip a specified number of characters. For example, suppose a text string "AYF0093.YoungMensApparel", if you want to find some of the text string that the first "Y" number, you can be start_num set to 8, so you do not find the serial number of the text part. FIND the first 8 characters from the beginning to find, but you can find at the next character find_text, then return to No. 9. FIND always returns the start within_text character number, if start_num greater than 1, will also skip the count of characters.

If find_text is empty text (), then FIND will return the value 1.

Find_text can not contain wildcards.

If there is no within_text find_text, then FIND returns the error value # VALUE!.

If start_num not greater than 0, then FIND returns the error value # VALUE!.

If start_num greater than the length of within_text, then FIND returns the error value # VALUE!.

Application example: the data containing different places, use "Find" function is very simple to determine the "province" to appear.

Explained

The formula "= FIND (province, A2)", the "Province" means the text you want to find a "province", (actual use, can also be very long string of characters). The object is to find to find the contents of cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ...", because there is no designated starting position, so the system from the first start. Back to the "3", said the "provincial" character in the third. The "Harbin, Heilongjiang Province ..." returns 4.

Find similar and, Search function also has the same function. The difference is, Find is case-sensitive, but Search is not case sensitive (when is the time to find the text is in English.)

In addition, Excel, text processing provides many functions to handle a particular double-byte characters (eg Chinese, Japanese) function, the function is generally added in the original "B", such as FIND, there a FINDB. Spoken before the LEFT, is the corresponding LEFTB and so on. In fact, the practical application, the use of non-"B" function would be sufficient. If you want to use with the "B" function, will have special attention, especially in the combination of the use function, which uses a function with a "B" form, the other has with "B" in the form of the function, all have to use The band "B" form, otherwise the result is likely to be wrong.