EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel LEFT in Excel
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

LEFT in Excel

By Madhuri ThakurMadhuri Thakur

LEFT Function in Excel

Excel LEFT Function (Table of Contents)

  • LEFT Function in Excel
  • LEFT Formula in Excel
  • How to Use the LEFT Function in Excel?

LEFT Function in Excel

There are many functions that are categorized under the TEXT category in the Formula tab of Microsoft Excel. LEFT is also one of the functions which are under head TEXT. It is widely used to extract a few letters or words as a result of output.

As we can see in the above screenshot that the LEFT function is where located. After putting the cursor itself, it defines as it returns the specified number of characters from the start of a text string. This function helps us to extract the substring from a string that starts from the very leftmost character.

This function can also be used with the help of VBA, which can be entered through the Microsoft Visual Basic Editor. A LEFT function can also be used by combining it with Excel functions like SUM, COUNT, DATE, VALUE, etc.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

LEFT Formula in Excel

Below is the LEFT Formula in excel:

LEFT Formula in Excel

The LEFT function uses the following arguments:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)
  1. TEXT: (required/mandatory argument)

It is the original text string or character from which we have to extract the substring or characters.

  1. NUM_CHARS: (Optional Argument)

It is the optional argument that refers to or defines the number of characters we wish to extract from the string or text. The default is 1. It starts with the leftmost character of the string or text; if this is omitted, then only one character will be returned.

Also, if the NUM_CHARS is greater than the character of a word, it would result in a whole string or word.

Result/Return Value:

After applying LEFT, the result will be either a string or letter or whole text depends on the requirement.

The types of LEFT function (where we can use) are the following:

  1. VBA Function (VBA)
  2. Worksheet Function

How to Use the LEFT Function in Excel?

LEFT Function in Excel is very simple easy to use. Let us now see how to the use LEFT function in Excel with the help of some examples.

You can download this LEFT function Excel Template here – LEFT function Excel Template

Example #1

Firstly, we will see the example of a LEFT function without an optional argument to make it clear of usage:

Step 1: We have to start the formula from “ =“, then type LEFT and then tab. The picture of Step 1 is below:

LEFT Function Example 1-1

Step 2: We can see in the above pic that it is asking for text, it means the word or string for which we have extracted the string, so we can use the cursor or put the address of the cell manually as well. Refer to the pic below:

LEFT Example 1-2

So, in the above picture, we are using the LEFT function for the cell address A5, which is written as Function.

Step 3: As we do not have to use the optional argument in this example, we have to just click enter or close the bracket or press enter. Both will have the same result.

Result of Example 1

We can see from the above picture that the result or output of the Formula is “F “. As we noted earlier in this article, if the optional argument whose syntax is num_chars is not given, it will automatically come as default 1. Hence the result is F, which is the first character of the Function from the left.

Example #2

Now, we will see the other scenario where the optional argument is greater than the number of words; then, it will return as a whole string or word.

LEFT Function Example 2-1

As we can see from the above example, the optional argument, which is num_chars, is written as 5; however, Eye’s example is only of three characters. Hence the result is the whole string which is Eye.

Result of Example 2

Example #3

Now, we will see the example with the optional argument (num_chars). How can we use it?

Suppose, in a report; we have to give only the initial of a given name, then in this scenario, the LEFT function will help us.

LEFT Example 3-1

Step 1: We will apply the LEFT formula in cell B4.

LEFT Function Example 3-2

Step 2: Now, we have to enter the syntax, cell address which is A4, for which we have to extract the substring. Like the below picture, we have to put a comma and then the number of characters that we want to extract. So, in this example, we want an initial so take it as 1.

Take the reference of below picture:

LEFT Example 3-3

Step 3: Just close the bracket to complete the syntax or just press Enter. The result would R, which is an initial or first character of Ram in the example. Please refer to the below pic:

LEFT Example 3-4

We can drag the formula by using Ctrl + D or double click on the right corner of the cell as shown below in red:

Press Ctrl + D

The result would be like below:

Result of Example 3

So, instead of doing it manually, we can use the function of the LEFT, which helps in time-saving because it will take lots of time if the data is huge.

Example #4

Suppose, in data, numbers and text are mixed, and we have to separate it. Then, in this case, the LEFT formula will help with the combination of the VALUE function.

Below is the example is given where 3 character is numeric, which we have to extract from a name.

LEFT Example 4-1

Now, we have to use the Formula with VALUE.

Suppose we wish to calculate in column D. So, the Formula would be:

=VALUE(LEFT(B3,3))

From the above formula, we can understand that B3 is the cell address from which we have to extract the string, and 3 is the character that we want.

Below is the picture with the formula:

LEFT Example 4-2

Now we can see the result after the application of this LEFT function with the combination of VALUE. Refer Below picture:

Result of Example 4

Hence, we can see that the result is 123 of cell address D3 because, after the application of the function, it extracts the three value from cell B3 from the leftmost, i.e. 123. Simultaneously, we can use this function for all the below cell, which could save lots of time.

Example #5

The LEFT Function can be used with the date as well. But the only condition is that it should be in number format, not in the text.

We can refer to the below pic for more clarification:

We can see the formula in cell address E4,

LEFT Function Example 5-1

The result is correct, which is 20 after application of LEFT Function with num_chars as 2,

LEFT Example 5-2

Whereas in E5, the same formula is used.

LEFT Example 5-3

But the result is 62, which not correct because D5 is in Text Format, not in number format like D4.

Result of Example 5

LEFT Function in Excel VBA

The LEFT Function, which we have seen above, is used in Worksheet.

Now, we will see in the VBA code in MS Excel.

Below is the formula or code which is to be used in VBA, shown as an example of Ball, so the result would B because the num_chars is defined as 1 in the below example.

LEFT VBA Code

Things to Remember

  1. The result or return value depends on the arguments which are inserted in the syntax.
  2. If the argument is less than (num_chars) 0, then the result would be a #VALUE error.
  3. The output or return value can be string or text.
  4. The LEFT function is used for extracting a substring from the string, which calculates from the leftmost character.

Recommended Articles

This has been a guide to LEFT in Excel. Here we discuss the LEFT Formula and how to use the LEFT function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. How To Use SEARCH Function in Excel?
  2. Guide To Excel OR Function
  3. Examples of Excel XIRR Function
  4. Excel NOT Function
  5. Examples of Excel Macros
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download LEFT function Excel Template

EDUCBA

Download LEFT function Excel Template

EDUCBA

डाउनलोड LEFT function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more