Text & String Manipulation
Extract the Single Word Containing a Specific Substring (@, #, SKU-)
Pull out the exact space-delimited token containing "@", "#INC-", or "ORD-" from unstructured log messages using the padded-space SUBSTITUTE + SEARCH + MID + TRIM island technique.
Isolate @emails, #ticket-IDs, and SKU tokens buried anywhere inside free-text notes!
Support tickets, bank wire memos, and Jira commit logs bury structured tokens inside variable-position prose—like "Escalated ticket #INC-4821 to tier-3 ops" or "Contact billing@northwind.io before Friday". Finding the marker with SEARCH("#", A2) gives its character offset, but finding where that word STARTS and ENDS normally requires gnarly nested searches for the preceding and trailing spaces!
The classic padded-island trick solves both boundaries in one stroke: wrap A2 in spaces (" " & A2 & " ") and widen every single space into 100 spaces via SUBSTITUTE(" " & A2 & " ", " ", REPT(" ", 100))! Now every word floats as an isolated island separated by 100-space oceans—so slicing 100 characters centered 50 characters left of SEARCH(B2, ...) and wrapping in TRIM(...) cleanly extracts ONLY the target word with zero neighbor bleed!
| Unstructured Log Note (A2) | Marker Substring (B2) | Padded Island Slice + TRIM | Extracted Token |
|---|---|---|---|
| "Escalated ticket #INC-4821 to tier-3 ops" | "#" | Isolates #INC-4821 island | "#INC-4821" |
| "Contact billing@northwind.io before Friday" | "@" | Isolates email island | "billing@northwind.io" |
| "Routine healthcheck passed cleanly" | "#" | ISERROR(SEARCH) Guard | "Token Not Found" |
Extract Space-Delimited Word Containing Marker Substring (Case-Insensitive)
Widens spaces to 100 characters, slices [-50, +50] around the matched marker, and trims surrounding whitespace.
=IF(OR(ISBLANK(B2), ISERROR(SEARCH(B2, A2))), "Token Not Found", TRIM(MID(SUBSTITUTE(" " & A2 & " ", " ", REPT(" ", 100)), SEARCH(B2, SUBSTITUTE(" " & A2 & " ", " ", REPT(" ", 100))) - 50, 100)))Strip Marker Prefix After Extracting Target Token (e.g., "#INC-4821" -> "INC-4821")
Substitutes the leading marker B2 out of the extracted token when you want the bare ID.
=IF(OR(ISBLANK(B2), ISERROR(SEARCH(B2, A2))), "Token Not Found", SUBSTITUTE(TRIM(MID(SUBSTITUTE(" " & A2 & " ", " ", REPT(" ", 100)), SEARCH(B2, SUBSTITUTE(" " & A2 & " ", " ", REPT(" ", 100))) - 50, 100)), B2, ""))Try the example
Use the original values or change them to test your understanding.