Cleaning imported data
LEFT function: extract a fixed-width prefix from structured codes
Pull a three-letter depot code from the start of consistent identifiers like NTH-014, and check what happens on shorter strings, blanks, and variable-length prefixes.
Consistent three-letter depot prefixes
A dispatch sheet tags shipments with NTH-014 and STH-209, where the first three characters always identify the depot. Include a short code (HQ) and an empty cell to verify edge behaviour.
| Shipment ID (A) | Extracted 3-char prefix |
|---|---|
| NTH-014 | NTH |
| STH-209 | STH |
| HQ | HQ |
Take the first three characters with LEFT
LEFT(text, num_chars) counts characters from the beginning of the text. For NTH-014, =LEFT(A2, 3) returns NTH. Omitting num_chars (=LEFT(A2)) defaults to 1 character and returns N.
=LEFT(A2,3)Short inputs, blanks, and the fixed-width assumption
When A4 holds only two characters (HQ), =LEFT(A4, 3) returns HQ without error or trailing spaces. When rows can be unpopulated, wrap with =IF(A5="", "", LEFT(A5, 3)) so empty inputs always stay blank. Passing a negative length such as LEFT(A2, -1) returns #VALUE!.
LEFT does not look for the hyphen—if a new depot uses a two-letter prefix like EU-014, LEFT("EU-014", 3) returns EU- including the hyphen. Use FIND("-", A2)-1 inside LEFT only when prefix lengths vary.
=IF(A5="","",LEFT(A5,3))Try the example
Use the original values or change them to test your understanding.