The LEFT function
The first characters of a text: the area code of a phone number, the prefix of a reference, the first name before the space.
Syntax
=LEFT(text, [num_chars])
In a French Excel: =GAUCHE(texte; [no_car]).
Arguments
- text: the cell.
- num_chars: how many characters; 1 by default.
An example
=LEFT(A2,FIND(" ",A2&" ")-1)
The first name in "Mary Smith": everything before the first space. The &" " avoids an error when there is a single word.
Pitfalls
- A number is read as its unformatted text: LEFT of a date returns the first digits of its serial number.
- Leading spaces throw the count off: TRIM first.
Going further
- MID, for the middle.
- Split or merge columns: split a whole column, with no formula.
- Find the right formula: "extract the first name".
Frequently asked questions
- How do I extract the last name?
=TRIM(MID(A2,FIND(" ",A2&" ")+1,LEN(A2)))returns what follows the first space.