Cleaning imported data
TEXTBEFORE and TEXTAFTER: extract variable-length text around delimiters
Pull city prefixes and trailing status tags from hyphenated codes of any length without nesting FIND inside LEFT or RIGHT, and handle rows missing a delimiter.
Variable-length prefixes where fixed LEFT(text, 3) fails
Dispatch tags like BERLIN-4029-URGENT and OSLO-88-STD vary in both city length and ticket-number length. Fixed character counts bleed across hyphens, whereas TEXTBEFORE and TEXTAFTER split cleanly on "-".
| Tag (A) | Before 1st "-" | After last "-" |
|---|---|---|
| BERLIN-4029-URGENT | BERLIN | URGENT |
| OSLO-88-STD | OSLO | STD |
| MANUALENTRY | Missing delimiter | Missing delimiter |
Read before the first delimiter and after the last (-1)
=TEXTBEFORE(A2, "-") returns everything before the first hyphen ("BERLIN" for A2, "OSLO" for A3). Passing -1 as instance_num in =TEXTAFTER(A2, "-", -1) searches from the right end and extracts the final segment ("URGENT") even if middle sections contain extra hyphens.
=TEXTBEFORE(A2,"-")Supply if_not_found when manual entries omit the delimiter
When A4 ("MANUALENTRY") contains no hyphen, TEXTBEFORE returns #N/A by default. Pass a fallback in the sixth argument—=TEXTBEFORE(A4, "-", 1, 0, 0, "Missing delimiter")—or return A4 itself when untagged entries should pass through.
=TEXTBEFORE(A4,"-",1,0,0,"Missing delimiter")Try the example
Use the original values or change them to test your understanding.