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:

Left(text, Num_Chars)
Right(text, Num_Chars)

The example below would make it clear:

left-and-right-formulas.JPG
These simple right and left formulas would only work when number of characters in each cell is the same, like here all the names have 5 alphabets. What if they are different??

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 :
=RIGHT(A3,LEN(A3)-FIND(” “,A3))

left-and-right-formulas2.JPG

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.

Share This

Popularity: 12%



Reader's Comments

  1. Daniel | December 3rd, 2009 at 4:28 am

    Another method is

    Data - Text to Columns - Choose Fixed Width you can do more than 2 splits this way but it’s only useful for fixed width values.

  2. Kartik | February 5th, 2010 at 6:00 am

    This is really helpful.

    Thanks

  3. jigyasu | May 18th, 2010 at 1:52 pm

    xcelent m8

Leave a Comment

Close
E-mail It