FormulaGym
XP 0
Streak 0

The LEFT function in Excel

LEFT pulls a set number of characters from the start of some text.

What LEFT does

It is useful for codes and IDs where the first few characters mean something, such as a location or a department. The result is always text, even when the characters are digits. Its relatives RIGHT and MID take characters from the end and from the middle.

Syntax

=LEFT(text, [num_chars])
text
The text to take characters from, usually a cell.
num_chars
Optional. How many characters to take. If left out, Excel takes 1.

Worked example

AB
1Order codeLocation
2LON-4821LON
3NYC-1190NYC
4PAR-7305PAR
5LON-2284LON
B2 =LEFT(A2,3) (copied down 4 rows)

LEFT takes the first three characters of each code. The formula is copied down so A2 becomes A3, A4 and so on.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Data cleanup

Split a prefix off IDs, product codes or postcodes.

Finance

Pull the account type from a ledger code.

Sales & Ops

Group orders by the location in their code.

Common mistakes

  • Expecting a number back. LEFT returns text, so digits it extracts will not add up until you convert them, for example with VALUE.
  • Using a fixed length on codes whose prefix length varies. Combine LEFT with FIND or LEN for those.
  • Forgetting that LEFT works on text. Applied to a number, it treats the number as text and returns text.

The LEFT course

  1. 1
    Pull out the first charactersFree practice below
    Practise
  2. 2
    RIGHT and MIDComing soon
  3. 3
    Split codes with FINDComing soon
  4. 4
    Clean exported IDsComing soon
  5. 5
    Combine with LENComing soon
  6. 6
    Build usernames from namesComing soon

Common questions

How do I take characters from the end of text?

Use RIGHT, for example =RIGHT(A2,4) for the last four characters.

How do I take characters from the middle?

Use MID with a start position and a length, for example =MID(A2,5,4).

Related functions