The Left function and Right function are simple functions which helps pick the characters from the beginning or ending of the characters in a cell as the case may be, whereas the Mid function helps pick characters in-between the cells.

Left Function:This function displays a specified number of characters from the left hand side of a piece of text.

Syntax = LEFT (Original Text, Number Of Characters Required)

Right Function:This function displays a specified number of characters from the right hand side of a piece of text.

Syntax =RIGHT(Original Text, Number Of Characters Required)

MID Function: This function picks out a piece of text from the middle of a text entry. The function needs to know at what point it should start, and how many characters to pick. If the number of characters to pick exceeds what is available, only the available characters will be picked.

Syntax =MID(Original Text,Start Number,Number Of Characters To Pick)

Example:

 Data Result Formula used What the formula does? Chartered Accountants’ Study Circle Chartered LEFT(A2,9) Results  in  9  characters  from beginning Chartered Accountants’ Study Circle Circle RIGHT(A3,6) Results  in  6  characters  from end Chartered Accountants’ Study Circle Study MID(A4,24,5) helps pick specific characters from the cell, say 5 characters from 24th Character

The Left function and Right function are simple functions which helps pick the characters from the beginning or ending of the characters in a cell as the case may be, whereas the Mid function helps pick characters in-between the cells.

For instance, in a Permanent Account Number (PAN), the possible results of 4th character of PAN can only be any one of the following:

 T AOP (Trust) C Company B Body of Individuals (BOI) P Person / Individual L Local Authority H HUF(Hindu Undivided Family) J Artificial Juridical Person F Firm G Govt A Association of Persons (AOP)

To test the 4th character is valid or not, we can use the mid function as follows:

 S. No. PAN Result Formula used 1 ABCPD1234D P MID(B2,4,1) 2 CEDCJ8568J C MID(B3,4,1) 3 BBBDF4567A D MID(B4,4,1)

Serial No. 3 gives the result as “D” which is not a valid 4th character. Thus it is evident that the PAN BBBDF4567A is invalid.

Note: All the PAN mentioned in the above example are dummy only and the algorithm to check if the PAN itself is valid is not made known to general public for security purposes. Hence we can use Excel to curb only apparent structural mismatch.

Search Function:

The SEARCH function locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Syntax =SEARCH(find_text,within_text,[start_num])

For example, to find the position of the letter “p” in the word “Sophistication”, you can use the following function:

Formula used =SEARCH(“p”,A2)

For extracting First Name of a Person: Use the formula =MID(A2,1,SEARCH(“ “,A2,1)) . In this example MID function searches the string at A2 position and starts the sub string operation from 1st character of the input string. For the length of extracted string Search Function is used which scans the string at C3 cell for the space (“ ”) character and returns its position as an integer value. And thus the resultant of both these functions fetches the person’s name.

For extracting Last Name of a Person: In the second part to extract the last name of a person use the formula =MID(C3,SEARCH(“ “,A2),100) . In this formula the MID function extracts from the cell no. A2. The start position is decided by the Search formula, here Search formula gives the position of space character (“ ”) and hence extraction starts from there and continues till the end of string.

Top 5 Cabinet ministers of India wherein First and Last name of the person are extracted:

 Ministers Formula used Result Formula used Result Narendra Modi MID(A2,1,SEARCH( " ",A2)) Narendra MID(A2,SEARCH( " ",A2),100) Modi Rajnath Singh MID(A3,1,SEARCH( " ",A3)) Rajnath MID(A3,SEARCH( " ",A3),100) Singh Sushma Swaraj MID(A4,1,SEARCH( " ",A4)) Sushma MID(A4,SEARCH( " ",A4),100) Swaraj Arun Jaitley MID(A5,1,SEARCH( " ",A5)) Arun MID(A5,SEARCH( " ",A5),100) Jaitley Venkaiah Naidu MID(A6,1,SEARCH( " ",A6)) Venkaiah MID(A6,SEARCH( " ",A6),100) Naidu

CA. Dungar Chand u. Jain
(The author is a Madurai based Chartered Accountant. He can be reached at dungarchand@hotmail. com)

BLOGGER
Name