Using LEFT and RIGHT Functions

Editing Text with LEFT & RIGHT Functions

The LEFT and RIGHT functions are very useful functions, allowing you to take some of the content of one cell, starting from the left or right end of that cell, and place that in another cell - the cell containing the LEFT or RIGHT function. These two functions can also be used together.

One of my favorite ways to use these functions is in creating customer, employee, and product numbers, based on parts of other cells – like part of the vendor name and then an item serial number to make a more useful product number or combining parts of employees’ last names and social security numbers to make an employee ID value that doesn’t divulge their full identity.

In this worksheet, we’re going to do just that – take the first four letters of each employee's last name and the last 4 digits of their social security numbers and combine them into a new employee ID value.

To begin, I’ll click in cell D4, for the first employee, and type the equal sign, followed by the word:

LEFT and press I’ll press the TAB key to insert the opening parenthesis, or I can type that opening parenthesis manually.

Let’s stop and look at the arguments here – Text, and Number of Characters. Text is the cell that contains the content I want to grab, and the number of characters is how many characters are in that cell, starting from the left side of the cell, that I want to grab.

So to provide the Text argument, I click on the first employee’s LAST NAME, adding cell B4’s address to the function, and then I type a comma, to separate the arguments.

After the comma, I type the number 4, to say I want the first 4 characters (the first 4 characters from the left) in the person’s last name.

Now I type a closing parenthesis to end the LEFT function.

But I don’t press Enter yet. Here’s where we start the RIGHT function and how I also show you a great way to use two functions together.

I type an ampersand (&) to tell Excel that another function is coming and that I want to use it with the first one.

So then I type:

RIGHT

And then the opening parenthesis, which invokes the RIGHT function, followed by clicking the cell I want to grab the right-hand characters from, which is cell E4, containing the employees’ social security number. Note that the arguments for the RIGHT function are the same as we saw for the LEFT function.

Next, I type a comma, and then supply the second argument for the RIGHT function – again, the number 4, telling Excel to grab the last 4 characters in cell E4, which are the last 4 digits of the employee’s social security number.

I then close the parentheses on the RIGHT function.

When I press Enter, the first employee’s code, Fabi9734 is created.

I can then use the Fill handle to repeat this for the rest of the staff, creating a new Employee ID for each person.