For function learners, how to construct nested functions to solve specific problems is a headache. I hope this article can help you clear your mind and write nested functions.

I remember that in middle school math problem solving, we often use auxiliary lines to solve problems. For the construction of nested functions, we can also use similar auxiliary cells to solve the problem step by step. The solution to the problem is as follows:

Judge the nature of the problem, choose the appropriate function, and set the general direction.

Decompose all the parts needed by the function and present them in different cells.

In the target cell, combine all the parts to get the result.

For example. The date in cell A2 in the table below is displayed as text instead of date format. We often export transaction data from some systems, but the exported date value results are in text form, not date format, which makes it impossible for us to use date formulas to deal with these data. In order to use the date formula, we need to first extract data from the text and then combine them into a date format function. (Of course, this problem can also be solved by the TEXTTOCOLUMN function, but it is beyond the scope of this article. )

The difficulty of this problem lies in how to get the value of the sky in the data. Through covert observation, we found that the value of the sky is between two "/". Then our task is to extract that text between the two "/". To do this, we need to know the position of the two "/"in the data. Let's do it step by step.

Enter the following formula =FIND("/ ",A2) in cell D4, and the result is 2, which means that the position of the first"/"in the text is the second character.

Then enter the following formula =FIND("/ ",A2, D4+ 1) in the D5 cell, and the result is 5, which means that the second"/"is the fifth character.

We build nested functions in the E2 cell. Note that when building nested functions, don't enter = in the cell first, so that Excel will only treat the input value as text, not as a formula, which can avoid the trouble of reporting errors in the building process. We use the MID function to take a value between two "/". The syntax of the MID function is

MID (extract the text of the character, the position of the starting character and the number of characters at the starting character position), please refer to the help document for specific usage.

Specific to our example, these three elements are

Text of the character to be extracted =A2

The position of the starting character = FIND("/ ",A2)+ 1 = 2+ 1 = 3. The reason for adding 1 is that the extracted characters include the characters where the starting character is located, and the characters after the first "/"are taken), in which find ("/",a2) can be pasted directly from D4 cell.

The number of characters extracted from the starting character position = find ("/ ",A2, D4+ 1)-find ("/", A2)- 1. The principle is that we find that the position of the second "/"is 5, the position of the first "/"is 2, and the subtraction between them is 3. This 3 includes. FIND("/ ",A2, D4+ 1) can be pasted directly from D5 cell without manual input.

After filling in these elements, our input values in E2 are shown in the figure below. Note that there is no equal sign.

When writing a formula, we will put the equal sign in front to get the result, as shown below.