Text & String Manipulation
Extract the Full Word Containing Specific Text (REPT Space-Island Method)
Pull an entire SKU, invoice tag, or email handle out of free-form memo text wherever a target prefix or substring appears—using the REPT(" ", 100) space-island pattern.
Why MAX(1, SEARCH(...) - 50) needs an upfront ISNUMBER(SEARCH) guard
The space-island idiom widens every single space in TRIM(A2) into 100 spaces via SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), locates the target substring inside that expanded string with SEARCH, steps back 50 characters, grabs a 100-character window with MID, and strips surrounding spaces with TRIM.
Crucial edge case: in spreadsheet engines where MAX ignores error arguments when another numeric argument is present, MAX(1, SEARCH(missing, big) - 50) can evaluate to 1 instead of propagating #VALUE!—returning the first word on a miss! Checking ISNUMBER(SEARCH(B2, A2)) upfront guarantees "NOT_FOUND" whenever the substring is absent.
| Memo Input | Substring Key | Extracted Token |
|---|---|---|
| "Paid wire for INV-88421 on Friday" | "INV-" | "INV-88421" |
| "Contact ops@arte-soft.com urgently" | "@" | "ops@arte-soft.com" |
| "General retainer adjustment" | "INV-" | "NOT_FOUND" |
Guarded Space-Island Token Extraction Formula
Guard with ISNUMBER(SEARCH(B2, A2)) first, then isolate the 100-character island centered on hit_pos.
=IF(ISNUMBER(SEARCH(B2, A2)), LET(big_txt, SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), hit_pos, SEARCH(B2, big_txt), TRIM(MID(big_txt, MAX(1, hit_pos - 50), 100))), "NOT_FOUND")Try the example
Use the original values or change them to test your understanding.