In column B, we have a list of projects with status.
The delimiter between the project and status is a hyphen surrounded by space.
The goal is to extract the project and status into separate columns.
First, let’s get the project name.
This will cause the formula to spill all results at the same time.
For thedelimiter, I need to use three characters: a space, a hyphen, and a space.
One way to handle this problem is to set the optionalmatch_endargument to 1.
Effectively, this makes TEXTBEFORE treat the end of the text like a delimiter.
When I update the formula, we get all the project names without errors.
Another way to handle this situation is to provide a value for the last argument, calledif_not_found.
I’ll first undo my previous change.
Then I’ll provide the range B5:B16 forif_not_found.
Now when a delimiter is not found, TEXTBEFORE returns the original value.
Next, let’s get the status with the TEXTAFTER function.
The values for text and delimiter are the same.
Like TEXTBEFORE, TEXTAFTER returns an error in cases where the delimiter is not found.
However, if I setmatch_endto 1, TEXTAFTER returns a blank result when the status is not found.
This works petty well, since the result accurately reflects the source data in column B.
But perhaps I want to return a custom value?
In that case, I can use theif_not_foundargument.
I’ll first remove thematch_endsetting.
To show a blank result, I can setif_not_foundto an empty string ("").
I can also pass in any value I like.
For example, I could use three question marks, “???”.
Or, the text “”.
One thing to be aware of is that thematch_endsetting will override theif_not_foundsetting.
For example, if I now setmatch_end,to 1 my custom value disappears, sincematch_endtakes precedence.
