LEFT Function in Excel Tutorial

Excel Tutorial

Introduction

The LEFT function in Excel extracts characters from the beginning of a text value. It is useful for extracting employee ID prefixes, product codes, invoice identifiers, and other information stored at the start of a text string.

In this LEFT Function in Excel Tutorial you will learn the LEFT function through practical business examples, including how to handle inconsistent capitalization and extra spaces.

LEFT Function Syntax

=LEFT(text, [num_chars])
  • text: The cell containing the text.
  • num_chars: The number of characters to extract. If omitted, Excel returns one character.

Question 1: Extract Employee ID Prefixes

An HR department uses employee IDs beginning with EMP. The team needs to extract the prefix to identify employee records.

Data:

Employee IDPrefix
EMP2045
EMP2046
EMP2047

EXCEL Formula in B2:

=LEFT(A2,3)

Copy the formula down.

Expected Result:

Employee IDPrefix
EMP2045EMP
EMP2046EMP
EMP2047EMP

Key Learning: LEFT extracts a fixed number of characters from the beginning of a value.

Question 2: Extract a Product Code from a Longer Reference

A warehouse stores product references in the format CODE-YEAR-BATCH. The product code is always the first three characters.

Data:

Product ReferenceProduct Code
LAP-2026-105
KEY-2026-210
MON-2026-315

EXCEL Formula in B2:

=LEFT(A2,3)

Copy the formula down.

Expected Result:

Product ReferenceProduct Code
LAP-2026-105LAP
KEY-2026-210KEY
MON-2026-315MON

Key Learning: LEFT can extract a code from a longer text value when the code always appears at the beginning.

Question 3: Extract the First Character from an Employee Name

An HR report needs the first character of each employee’s name for a simple initial column.

Data:

Employee NameInitial
Ananya Gupta
Bharat Mehta
Charu Malhotra

EXCEL Formula in B2:

=LEFT(A2,1)

Copy the formula down.

Expected Result:

Employee NameInitial
Ananya GuptaA
Bharat MehtaB
Charu MalhotraC

Key Learning: Use 1 as the second argument when you need only the first character.

Question 4: Extract Characters When the Text Is Shorter Than Requested

The LEFT function does not return an error just because the text contains fewer characters than the number requested.

Data:

Product NameFirst Four Characters
Pen
Notebook
Monitor

EXCEL Formula in B2:

=LEFT(A2,4)

Copy the formula down.

Expected Result:

Product NameFirst Four Characters
PenPen
NotebookNote
MonitorMoni

Key Learning: If the text contains fewer characters than requested, LEFT returns the characters that are available.

Student Practice

Use the following product references:

Reference
INV-10582
CUS-20473
SUP-30864

Complete these tasks:

  1. Extract the first three characters.
  2. Extract the first character.
  3. Convert the first three characters to lowercase.

Formulas:

=LEFT(A2,3)
=LEFT(A2,1)
=LOWER(LEFT(A2,3))

Key Takeaways

  • LEFT extracts characters from the beginning of a text value.
  • Use =LEFT(A2,3) to extract the first three characters.
  • Combine LEFT with UPPER or LOWER to standardize capitalization.
  • Combine LEFT with TRIM when the text contains leading ordinary spaces.
  • If the text is shorter than the requested number of characters, Excel returns the available characters.
  • LEFT function in excel returns text, even when the extracted characters look like numbers.

Frequently Asked Questions (FAQs)

1. What does the LEFT function do in Excel?

It extracts a specified number of characters from the beginning of a text value.

2. What is the syntax of LEFT?

=LEFT(text, [num_chars])

The second argument specifies the number of characters to return.

3. What happens if I omit the number of characters?

Excel returns the first character because the optional num_chars argument defaults to 1.

4. Can LEFT extract employee ID prefixes?

Yes. If an ID begins with EMP, use =LEFT(A2,3) to extract the prefix.

5. Can LEFT return uppercase characters?

Yes. Use =UPPER(LEFT(A2,3)) to extract three characters and convert them to uppercase.

6. How can I use LEFT when a cell contains leading spaces?

Combine it with TRIM, for example: =LEFT(TRIM(A2),3).

7. What happens when the text is shorter than the requested length?

Excel returns the available characters. For example, =LEFT("Pen",4) returns Pen.

8. Does LEFT change the original cell?

No. The formula returns the extracted text in another cell and leaves the original value unchanged.