Abstract
Transcript
In this video, we'll look at some advanced options for the TEXTBEFORE and TEXTAFTER functions.
In this first example, let's look at how to configure TEXTBEFORE and TEXTAFTER to handle situations when a delimiter is not found.
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 is a task for the TEXTBEFORE function
For the text, I'll go ahead and use the range B5:B16. This will cause the formula to spill all results at the same time.
For the delimiter, I need to use three characters: a space, a hyphen, and a space.
This configuration works in most cases, but notice that two projects in the list don't have a status, and fail with an N/A error because TEXTBEFORE cannot find the delimiter.
One way to handle this problem is to set the optional match_end argument 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, called if_not_found.
I'll first undo my previous change.
Then I'll provide the range B5:B16 for if_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 set match_end to 1, TEXTAFTER returns a blank result when the status is not found.
This makes sense, since TEXTAFTER now treats the end of the text as a delimiter, and there is nothing after that.
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 the if_not_found argument.
I'll first remove the match_end setting.
To show a blank result, I can set if_not_found to an empty string ("").
I can also pass in any value I like. For example, I could use three question marks, "???".
Or, the text "<no status>".
One thing to be aware of is that the match_end setting will override the if_not_found setting.
For example, if I now set match_end, to 1 my custom value disappears, since match_end takes precedence.
