 Tweet

# Excel Tutorial How to Use Nested Functions

In a formula when the result of one function is used as an argument to another function then the function is said to be nested. Nesting is allowed up to 64 levels in excel 2013.

In this example, by looking at the title we are trying to get the gender. Nested functions example data
=IF(LEFT(A2,SEARCH(".",A2,1)-1)="Mr","MALE","FEMALE")

We used text functions LEFT, SEARCH which are said to be nested in this example. (For more details on these Text functions please refer the article Text Functions)

In the example, SEARCH function is nested into LEFT Function. LEFT is nested into IF.

As mentioned above, the outcome of nested function serves as an argument to the main function.

Here the outcome ofSEARCH(".",A2,1)-1) will be 2.

This 2 becomes an argument to LEFT i.e., LEFT(A2,2) gives Mr Hence in IF(Mr=Mr,MALEFEMALE), logical test give TRUE so final result of the formula is MALE.

Syntax of SEARCH is =SEARCH(find_text, within_text,[start_number])

It returns the number of the character at which appears in for the first time after the , reading left to right. So in this example SEARCH returns the character position of .. Hence to get the title LEFT(A2,SEARCH(".",A2,1)-1) is used. -1 limits the LEFT function to return the A2 content up to a position before the . Similarly, First name can be separated by using nested SEARCH for . and   within MID. Syntax of MID is =MID(text,start_num,num_chars). So to get start_num SEARCH(".",A2)+1 is used and to get number of characters in first name SEARCH(" ",A2)-SEARCH(".",A2) is used. IFERROR function is nested here to handle the error encountered while SEARCHing non-existing  .

Challenge: Reader can try to get last name from the name(especially for the name in A5) Ans:
=MID(A5,IFERROR(SEARCH(" ",A5)+1,SEARCH(".",A5)+1),LEN(A5)-IFERROR(SEARCH(" ",A5),SEARCH(".",A5))) For detailed illustrations on SEARCH, LEFT, MID, LEN please refer to Text Functions    