LEFT: take characters from the beginning of a value
LEFT is handy when a code has a stable prefix. It is less reliable when you are guessing the location of a delimiter or cutting apart names with varying lengths.
Formula syntax
=LEFT(text, [num_chars])
What goes into it
text
Text to extract from.
num_chars
Optional number of characters from the left; defaults to 1.
A quick example first
=LEFT("CellLingo",4)
Returns “Cell”.
=LEFT(A2,FIND("-",A2)-1)
Returns text before the first hyphen when one exists.
Extract the prefix from a product code
Enter these sample codes in A2:A4. They deliberately include a missing separator.
| Cell | Code |
|---|---|
| A2 | INV-0042 |
| A3 | PO-019 |
| A4 | UNASSIGNED |
| Formula | Expected result / interpretation |
|---|---|
| =LEFT(A2,3) | INV |
| =LEFT(A3,FIND(“-“,A3)-1) | PO |
| =IFERROR(LEFT(A4,FIND(“-“,A4)-1),”Review code”) | Review code |
A fixed length works only when the prefix width is fixed. FIND locates the first hyphen and subtracting one excludes it from the output. The missing separator produces an error before LEFT can extract anything, which is why a deliberate review label is more useful than a silent blank here.
Before you copy this into a real sheet
Define what multiple hyphens mean before adopting the formula: it always uses the first. If a code begins with a hyphen, the prefix is empty. Extracted digits remain text, which is often desirable for identifiers. Do not wrap every extraction in IFERROR until you have checked for other data defects. For newer Excel installations, TEXTBEFORE can express the same delimiter-based intention more directly, but check recipient version support first.
Useful habits
- Combine with FIND/SEARCH when the cut position depends on a delimiter.
- Validate num_chars when it is calculated from another function.
- For structured splitting, newer TEXTBEFORE may be clearer when available.
Where people get caught out
- Assuming LEFT understands words or delimiters by itself.
- Forgetting that missing delimiters can make a FIND-based length expression error.
Other functions worth a look
A detail worth remembering
A code with a variable-length prefix is a different problem from a fixed-length code. Check a few unusual rows before filling down.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.