Learn to create unique identifiers for your data entries using the LEFT and RIGHT functions in Excel. This method is particularly useful when dealing with sensitive information such as employee details, as it allows you to create an ID that does not divulge the employee's full identity.
Key Insights
- The LEFT and RIGHT functions in Excel allow you to extract specific parts of the content in a cell, starting from either end of the cell.
- These functions can be utilized to create unique identifiers such as customer, employee, and product numbers based on parts of other cells.
- For example, you can use parts of a vendor name and an item serial number to make a useful product number.
- Or you could combine parts of employees’ last names and social security numbers to make an employee ID value that doesn’t divulge their full identity.
- The LEFT and RIGHT functions require two arguments: the Text, which is the cell that contains the content you want to grab, and the Number of Characters, which is how many characters you want to grab, starting from either end of the cell.
- You can use two functions together by typing an ampersand (&) to tell Excel that another function is coming and that you want to use it with the first one.
LEFT/RIGHT Patterns
-
LEFT(text, n)
Returns first n characters from left.
-
RIGHT(text, n)
Returns last n characters from right.
-
MID(text, start, n)
Returns n characters starting at position.
-
Combined with FIND
Dynamically extract before/after a delimiter.
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.
