Excel track · Exercise 49 of 86 Paid
LEFT + FIND: pull out a prefix
Pull a prefix of any length out of a code with LEFT and FIND.
Advanced
Order log
Read-only data · answer in B6
In B6, extract the three-letter prefix from the reference in B2 (everything before the first dash).
| A | B | C | |
|---|---|---|---|
| 1 | Order | Reference | Customer |
| 2 | SO-1 | ORD-2024-0917 | Ana |
| 3 | SO-2 | INV-2024-0612 | Bo |
| 4 | SO-3 | ORD-2023-1180 | Cy |
| 5 | |||
| 6 | Prefix for SO-1 |
Reference answer
What it does
FIND reports where a character sits inside a string, and LEFT takes a given number of characters from the start. Joined, they cut a prefix of any length off a code. The reference in B2 is ORD-2024-0917, so FIND("-",B2) returns 4, counting from one.
The dash is not part of the prefix, so the length is 4 minus 1. The formula =LEFT(B2,FIND("-",B2)-1) returns ORD in B6, and the minus one is the only thing keeping the dash out. Type the position FIND gave you and the answer comes back as ORD-.
LEFT(B2,3) gives the same answer on every row of this sheet, which is why it feels safe. It breaks the first time a code arrives with a four-letter prefix. FIND keeps the formula tied to the dash instead.
Syntax
=LEFT(text, FIND(delimiter, text) - 1)Worked example
On Order log, =LEFT(B2,FIND("-",B2)-1) returns ORD.
=LEFT(B2,FIND("-",B2)-1)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The search string is absent from the cell, and FIND raises #VALUE! rather than returning 0. |
Off by one | LEFT(B2,FIND("-",B2)) keeps the dash, so the answer reads ORD- instead of ORD. |
No FIND | LEFT(B2,3) answers ORD here and drops the first letter of a four-letter prefix. |
At work
Reference ids, ticket keys and SKU families: LEFT and FIND pull a coded prefix out without a helper column.