This article talks in great detail about excel’s Left and Right formula’s and explains how by combining “Find” and “Len” functions with Left and right we can easily handle more comlicated situations which may arise.
While concatenate function helps us join the characters of multiple cells, we can use Left and Right Formulas to break them down.
Let’s take the same example that we used to explain concatenate function wherein, we had the first and last name in different cells and we wanted to combine them. What if we have them in one cell and we wish to separate them??
Left and Right function can help us do that.
The syntax of these 2 functions are as follows:
The example below would make it clear:
We can handle this by expanding our left and right formulas.For the first name we want it to pick all the characters until we have a space. So if we enter
=LEFT(A3,FIND(” “,A3)-1) in column B we will get the first name.
For the last name we can not do the same thing because the “Find” function only counts the number of character from the left. So in the above example =RIGHT(A3,FIND(” “,A3)-1) would return “terson”. To get around this, we can add the “LEN” command to our formula. Len function counts the number of characters of a cell. So if we subtract find from len we would get the last name.
The formula would be :
We can also use the above formula when there is a comma instead of space i.e. we had “Ashley,patterson” in column A. In that case we woud just replace ” ” in the find function by a comma.