20171202

LEFT-MID-RIGHT in Excel

At times in excel as a part of data cleansing or analyzing, we come across to split cell values in to pieces. Excel has in-built function out of which we can nest these functions and create our own formula.







In a Data set we may have a values combining with Full name and Employee ID, Account number and we need to extract the Full name in one column and Account number into another column, when it comes with large size of data set, this becomes a difficult and time taking task to split the values.

In Excel we can use text function since the values combining with text and numbers are treated as alphanumeric and we can use text function to split values.

Example:
       ABC Co. Lte CS242335

In this example ABC Co. Lte is name of company & CS2422335 is Account identification number.

Simply put these values in =RIGHT(cell_name, 8) to extract Account ID. As we know that ID is fixed with 8 digits. But if this is not fixed, we need to dip dive and create a nested function smartly.







Now analyze the below example for better understanding and apply the same.











This method of creating nested formula helps us to split the string may it be left string, mid or right string.

Let us analyze and understand the formula used to extract left part (the first name of Employee)
      =LEFT(B3,FIND(" ",B3,1)
                 Assigning the value in cell B3, we tried to look for first space " " from left side and extract left value to the first space.

      =MID(B3,FIND(" ", B3)+1,LEN(B3)-6-FIND(" ",B3)+1
                   Let us understand this formula
        Syntax =MID(text, start_num, num_chars)

Evaluate the formula used:
       text            = MID(B3
       start_num    = FIND(" ", B3)+1 here are asking to look for first " " and +1 this means if found
                           then add 1 to the number of character from space. This is the starting point
       num_chars   = LEN(B3)-6-FIND(" ",B3)+1, extract the number of characters out of total
                           length of string by excluding last 6 chars

     =RIGHT(B3, 6)= extract first 6 characters from right side out of total length of characters in string


Hope this is useful, please subscribe for more updates on easy method to work on Excel functions.

Download Practice workbook LEFT-MID-RIGHT on Google Drive




No comments:

Post a Comment

Search This Blog

Reverse String using Excel VBA

Normally this is an interview question being asked by company technical recruiters. This is an logical question and every aspirants must b...