FIND
Where is it?
Locate a marker inside a value so a formula can cut at it
A number: the position of the first character of the text you looked for
Syntax
=FIND(find_text, within_text, [start_at])
| Argument | Required | What goes here |
|---|---|---|
find_text | Required | The text you are looking for, in double quotes, like "-". |
within_text | Required | The cell to look inside, usually A2. |
start_at | Optional | Where to start looking, when you want the next one along. |
How to use FIND
FIND returns the position of what you searched for, counting from 1. It is almost never the answer on its own. It is the argument you feed to MID, LEFT or RIGHT so they cut in the right place.
The optional third argument says where to start looking, which is how you reach the second dash rather than the first: find the first, then start again one character past it.
FIND cares about capitals. SEARCH is the same function without that, and SEARCH also accepts wildcards.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=FIND("-",A2)Where the first dash sits. | 3 |
=FIND("-",A2,FIND("-",A2)+1)Where the second one sits. Start looking one past the first. | 8 |
=LEFT(A2,FIND("-",A2)-1)Everything before the first dash. Subtract 1 or you take the dash as well. | GL |
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)The piece between the two dashes. Long, but it holds whatever length the pieces are. | 6100 |
Notes
- FIND returns an error when the text is not there at all, which is a useful signal rather than a nuisance: it says this row is not laid out like the others.
- FIND matches capitals exactly. Use SEARCH when the import is inconsistent about them.
Related
A reference tells you what it does. A unit makes you use it.