Happy #formulafriday Excellers.
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?
The Excel LEFT Function extracts a given number of characters from the left side of a supplied text string. Here is quick example
=LEFT(“SUMMERTIME”,5) will return SUMMER.
The LEFT Function Syntax.
The syntax for this formula is
=LEFT(text, [num_chars])
text is the string you want to extract from (that is the cell reference that holds your text string)
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.
LEFT Function Examples.
1. Extract Names From An Email Address.
If you want to extract a names from an email address, we can easily do that using the LEFT Function along with the FIND Function. Here is an extract of my email addresses:
The formula looks like this
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.
The formula looks like this.
This is a super easy but clever formula. First of all
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 LEFT function has the argument num_chars, but the argument is optional and left will extract 1 character from the left when it is absent. This results in the text string “mcooper”
The domain name is added and all of the characters are converted to lower case using the LOWER function.
3. Force Users To Enter A Pre – Fix To Their Data Entry
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.
Here is my example data setup. I have some bike parts in my inventory, and they all begin with the prefix “BP”
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.
Notes About LEFT Function.
- LEFT also works on numbers you can extract digits from numbers
- The default value for [num_chars] is 1 if you leave this argument blank
- Any number formatting is not counted in the [num_chars] argument. For example $ will not be counted
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
