You are here: Home / Blog / Formula Friday – Lets Take A Left…..Excel Function…..
Formula Friday – Lets Take A Left…..Excel Function…..
Today we are going a little left of field, and looking at the really useful LEFT Function in Excel. This a a really useful function to have in your Excel toolbox which I use more than I thought I would over the years for manipulating and extracting data in my Excel worksheets.
What Does The LEFT Function Do?
num_chars is the number of letters you want to extract from the full string. Note the [ ] brackets. This means that this is an optional argument, which if left out default the argument to 1.
Let's run through some useful and interesting examples for how to use LEFT in Excel.
1. Extract Names From An Email Address.
First of all FIND looks for the @ sign in the email address, so in the first example @ is at character number 7, we use the -1 so that Excel does not count the @ sign as a character.
The LEFT function uses the result of the FIND function ( in this cases the number 7) to extract the first 7 characters form the email address. Cool huh???.
2. Create An Email Address With A Username And Domain.
We can use the LOWER Function along with the LEFT Function to automatically create email addresses from a list of usernames. Here is the list of usernames we want to create email addresses for.
Working from the inside out, this formula first concatenates the last name in column C with the first letter of the first name from column B.
The domain name is added and all of the characters are converted to lower case using the LOWER function.
This is a really cool use of the LEFT Function, forcing users to use a prefix for their data entry into worksheet cells. We can combine the EXACT Function with the LEFT Function using a data validation rule with a custom formula, which is triggered when a user tries to enter a value into a worksheet cell.
When updating new parts into the inventory we can force the user to enter the prefix BP by using data validation.
So when a user attempts to enter a value into the Part Number Column, the LEFT Function extracts the first 3 characters of cell C7, next the EXACT Function is used to compare the extracted text to the hard coded "BP-" we have used and performs a case sensitive comparison. If the two text strings match then EXACT returns TRUE and the cell entry is validated as correct. If it returns FALSE and fails then the user is warned that the data entry cannot proceed.