LEFT
Returns the first characters of text: a category code from a product SKU, a prefix from a number, the first letter of a name.
LEFT(text, [num_chars])- [ ]optional part
- text
- Where to take the characters from, for example cell A2.
- num_chars
- How many characters to take from the start. If left out, you get one.Optional
Return value
Text: the given number of first characters. If the number is left out, the function returns one character.
Examples
Category from a SKU
fx
A
B
C
1
2
3
4
5
6
7
SKU
Category
CHR-1042
TBL-2210
CAB-0381
CHR-1187
Sheet1
Details
LEFT takes as many characters from the start of the text as the second argument says. Spaces, dashes and periods count as characters too.
If the second argument is left out, the function returns just the first character.
The result is always text, even if you cut out digits. To calculate with it as a number, wrap the formula in VALUE: =VALUE(LEFT(A2,4)).
Common mistakes
- The beginning has a different length in different rows. A fixed number won't work: find the position of the separator with FIND and subtract 1.
- The digits you cut out stay text. Comparing them with a number gives FALSE, and VLOOKUP with a numeric key won't find them.
- An extra space at the start of the cell takes up one character. Remove it with TRIM first.