Formula Friday – Lets Take A Left…..Excel Function…..


excel-formula-crash-course-join-today-v1

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:

FORMULA FRIDAY USE LEFT FUNCTION TO FIND NAME FROM EMAIL ADDRESS

 

The formula looks like this

FORMULA FRIDAY USE LEFT FUNCTION TO FIND NAME FROM EMAIL ADDRESS1

 

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.

FORMULA FRIDAY CREATE EMAIL ADDRESSES WITH LEFT FUNCTION NAMES AND A DOMAIN1

The formula looks like this.

FORMULA FRIDAY CREATE EMAIL ADDRESSES WITH LEFT FUNCTION NAMES AND A DOMAIN2

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”

 FORMULA FRIDAY FORCE DATA VALIDATION PREFIX WITH LEFT FUNCTION1

When updating new parts into the inventory we can force the user to enter the prefix BP by using data validation.

FORMULA FRIDAY FORCE DATA VALIDATION PREFIX WITH LEFT FUNCTION2

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.

  1.  LEFT also works on numbers you can extract digits from numbers
  2.  The default value for [num_chars] is 1 if you leave this argument blank
  3.   Any number formatting is not counted in the [num_chars] argument.  For example $ will not be counted

excel-formulas-ad-5

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.

1

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.

 

 

More Excel Tips_New1
Learn Excel With Our Excel Online Courses

 

 

master_728x90

 

Personal macro workbook not loading automatically

Learn Excel Dashboard Course

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts