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
| A | B | |
|---|---|---|
| 1 | Order code | Location |
| 2 | LON-4821 | LON |
| 3 | NYC-1190 | NYC |
| 4 | PAR-7305 | PAR |
| 5 | LON-2284 | LON |
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
- 1Pull out the first charactersFree practice belowPractise
- 2RIGHT and MIDComing soon
- 3Split codes with FINDComing soon
- 4Clean exported IDsComing soon
- 5Combine with LENComing soon
- 6Build 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).