Excel Formula Exercises 1
I am starting a new series of posts for you to sharpen your skills with Excel formulas. Each post will include 4-5 excel formula exercises to give you a quick workout without getting boring.
For this first post I prepared some exercises for excel text functions.
- We have a column of data that contains name and surnames of our employees. We want to separate names and surnames of our employees to separate columns. How can we do it?Here is the formula for CellB2:=LEFT(A2,SEARCH(” “,A2,1)-1)
In this formula, Left Function fetches a certain amount of characters starting from left in the text in cell A2. Search Function is used to determine how many characters to fetch based on the position of the blank space between name and surname.
- We need to calculate expiration date for our items. But dates are not entered with date format. So how do we make that calculation with one formula?Here is the formula for CelD2:=(LEFT(C2,2)&”/”&MID(C2,3,2)&”/”&RIGHT(C2,4))-(LEFT(B2,2)&”/”&MID(B2,3,2)&”/”&RIGHT(B2,4))
This formula fetches day, month and year parts of the dates in columns B and C, then reconstructs them in date format. When you evaluate the functions in this formula, it will become the one below:
- We copied our 7 digit item codes from a text file into Excel. And it deleted zeros in front of our item codes. How can we get them back?Here is the formula in CellB2:=IF(LEN(A2)>5,”0″&A2,”00″&A2)
This formula checks whether item code has more than 5 digits or not. If the item code is longer than 5 digits, it will add a zero in front of the it. If the item code is not longer than 5 characters, than it will add 2 zeros in front of it.
- We have a bunch of army officers that are promoted. So we need to update our list of officers with their new ranks. How do we do it?Here is the formula in CellB2:=REPLACE(A2,1,7,”Major”)
This formula replaces 7 characters (starting from 1st character) of the text in Cell A2 with Major.
Please feel free to ask any formula related questions below the post via comments section.