Pull the ref out of a tracking code (MID + FIND)
The problem
Problem statement
Every tracking code in A2:A5 packs three fields: a prefix, the invoice ref inside square brackets, and a country suffix. Your job is the middle one only — the ref for the code in A2, in the Ref cell B2.
The ref length varies and the country suffix changes, so nothing may be hardcoded: the formula must locate both brackets itself.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | |
|---|---|---|
| 1 | Tracking code | Ref |
| 2 | ORD[INV-4821]CN | |
| 3 | ORD[INV-4822]US | |
| 4 | ORD[INV-4823]DE | |
| 5 | ORD[INV-4824]FR |
Notes
MID(text, start_num, num_chars) needs a start and a length. FIND gives you the start (the position after the opening bracket) and, used twice, the length (closing position minus opening position minus one). FIND is case-sensitive; SEARCH is the case-insensitive twin.
How to approach
Your answer goes in B2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is B2
- FIND("[", A2) gives the position of the opening bracket; the ref starts one character later
- The length is FIND("]", A2) - FIND("[", A2) - 1
- Shape: =MID(?, ?, ?) — both the start and the length come from FIND
Official solutions
Show 2 solutions — try it yourself first
Approach 1: one FIND for the start, two for the length
=MID(A2, FIND("[",A2)+1, FIND("]",A2)-FIND("[",A2)-1)
For ORD[INV-4821]CN: FIND("[") is 4 so the ref starts at 5; FIND("]") is 13, so the length is 13 - 4 - 1 = 8. MID(A2, 5, 8) is INV-4821.
Approach 2: FIND's third argument starts the search later
=MID(A2, FIND("[",A2)+1, FIND("]",A2,FIND("[",A2))-FIND("[",A2)-1)
FIND("]", A2, FIND("[",A2)) searches for the closing bracket from the opening one onwards. Useful when the text can contain an earlier ] that is not the delimiter you mean.
Function guide
FAQ
How do I extract text between two characters in Excel?
Use MID with FIND for both ends: =MID(A2, FIND("[",A2)+1, FIND("]",A2)-FIND("[",A2)-1). The +1 skips the opening bracket; the -1 removes the closing one from the length.
FIND or SEARCH?
FIND is case-sensitive and does not accept wildcards; SEARCH ignores case and does. Delimiters like [ ] behave the same in both, so pick FIND when case matters elsewhere in the same formula.