What You Will Learn
Five project notifications in A2:A6 — each starts with a tag like "URGENT:" or "FINAL:" followed by a description. You need to extract just the tag into column B.
The tag ends at the first space. FIND locates that space, and LEFT returns everything before it:
=LEFT(A2, FIND(" ", A2) - 1)
For "URGENT: Project Audit": FIND returns 8 (the space is at position 8), minus 1 gives 7. LEFT returns the first 7 characters: "URGENT:".
Fill this down from B2 to B6. Each row gets its own tag extracted regardless of tag length — "FINAL:", "DRAFT:", "REVISION:", "HOLD:" are all different lengths but the same formula handles them all.
LEFT + FIND isolates the tag by stopping at the first space.
The audit formulas:
- B9:
=COUNTA(A2:A6) — total notifications.
- B10:
=COUNTIF(B2:B6,"URGENT:") — counts how many are urgent.
Once the tags are in their own column, you can filter or count by priority instantly. Without extraction, counting urgent alerts would mean reading every notification.
The -1 after FIND(" ", A2) is easy to forget but important. FIND returns the position of the space itself — if the space is at position 8, LEFT with 8 would include the space in the result. Subtracting 1 gives 7, which returns only "URGENT:" without the trailing space. Without -1, your tags would have an extra space at the end.
Alternative: Target the Colon
Since every tag in this dataset ends with a colon, you could split on that instead:
=LEFT(A2, FIND(":", A2))
This returns the same result for this dataset but includes the colon. The space-based version is more flexible because it works for any first-word extraction, not just colon-terminated tags.
Alternative: TEXTBEFORE
In Excel 365, TEXTBEFORE does the same thing in one argument:
=TEXTBEFORE(A2, " ")
Returns everything before the first space. Cleaner but only works in newer Excel versions.
If a cell does not contain a space at all, this formula returns an error. In this dataset every entry has a space after the colon, so the split point is reliable.
Extract the leading tag from each notification in column A, then complete the summary so the sheet shows how many alerts exist in total and how many of them are marked URGENT:.