What does it do?
The Excel LEFT function extracts a number of characters from the left most side of a text string. The number should be a whole number and greater than zero. Spaces and special characters in the cell values are counted, but any cell formatting is excluded. In short, the cell value is taken into account and not the cell display, this is particularly true of a cell with a date in it.
Syntax Explained
Text: This is the text string where characters from the left will be extracted.
Num_chars (optional): This is the number of characters to extract from the left side only of the text string. If it is left blank, it will default to 1. The 1 represents the first character in the text string.
Examples
1. LEFT covering many types.
In the example above I have set up a few text strings to extract characters from:
- Row 3 = Extracts the first 4 characters.
- Row 4 = Extracts the first 7 characters, spaces count as characters.
- Row 5 = Extracts the first 3 characters, note the cell format is set to show a “£” sign, this is excluded.
- Row 6 = Extracts the first 2 characters from the date value only. All dates in Excel are made up of a 5-digit number, the dd/mm/yyyy format is a cell display only.
- Row 7 = Extracts the first 5 characters, as above, since the cell display is set to hh:mm:ss, the return will extract from the cell value only. In this case, the decimal point is also considered as a character.
Pro Tip
Combining, or using alternatives to the LEFT functions such as SEARCH, FIND, MID, LEN and RIGHT can help support text extraction techniques. When used correctly they can make light work of something that would take hours/days using manual methods.