Arte SheetsLearn. Try. Understand.

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. This shortcut assumes space-delimited tokens no longer than 50 characters and a nonempty marker. SEARCH supports wildcards. Punctuation remains attached to the word; use a proper parser for more general input.

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 + TRIMExtracted 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)))

Remove All Marker Occurrences from the Extracted Token

SUBSTITUTE removes every occurrence of the marker, not just a leading prefix. Use a different formula if internal occurrences must be preserved.

=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.

Further reading