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
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