Text
Extract words starting with #, @, or a prefix (#hashtags, @mentions, Jira IDs)
Pull the 1st, 2nd, or Nth #hashtag, @mention, or PROJ- ticket code from free-form notes using padded " " & TRIM(A2) & " " with TEXTAFTER and TEXTBEFORE.
Pad leading and trailing spaces so boundary words and mid-word symbols never collide!
Padding ordinary spaces handles a token at the beginning or end of the string while excluding a prefix embedded within a word.
This is a space-delimited token extractor, not a full hashtag or mention parser.
| Note Text | Prefix + Instance | Extracted Token |
|---|---|---|
| "#ops-412 Fixed Item#9 latency with @maya and #perf" | "#" (1st) | #ops-412 (skips Item#9!) |
| "#ops-412 Fixed Item#9 latency with @maya and #perf" | "#" (2nd) | #perf |
| "#ops-412 Fixed Item#9 latency with @maya and #perf" | "@" (1st) | @maya |
Extract Nth prefixed word (#hashtag, @mention, or ticket prefix)
Pass prefix ("#", "@", or "JIRA-") in B2 and occurrence number (1, 2, ...) in C2; IFERROR returns "None" when fewer tokens exist.
=IF(OR(B2="",C2<1,C2<>INT(C2)),"Invalid inputs",LET(s, " " & TRIM(A2) & " ", after, IFERROR(TEXTAFTER(s, " " & B2, C2), ""), IF(after="", "None", B2 & TEXTBEFORE(after, " "))))Count how many words begin with prefix B2 in the cell
Compare string length before and after removing " " & Prefix inside padded " " & TRIM(A2).
=LET(s, " " & TRIM(A2), (LEN(s) - LEN(SUBSTITUTE(s, " " & B2, ""))) / LEN(" " & B2))Inputs and interpretation
Tokens here are delimited by ordinary spaces. Tabs, line breaks and surrounding punctuation are not parsed as language-aware boundaries, and trailing punctuation remains attached. Use a nonempty prefix without spaces and a positive integer occurrence. A lone prefix is treated as a token by this simple rule, not validated as a real social-media identifier.
Try the example
Use the original values or change them to test your understanding.