There are three new text formulas in Excel. TEXTSPLIT, TEXTBEFORE, and TEXTAFTER. These allow you to do things easily that were previously difficult and often involved multiple nested formula.
TEXTSPLIT
TEXTSPLIT allows you to take text from one cell, and split it into multiple cells based on a delimiter. A delimiter is just a fancy name for “the thing that is separating each part”. So, in a name for example, we would have spaces between the first name and the last name, so space would be the delimiter. If you had a list of emails, separated by commas, then comma would be the delimiter.
To use TEXTSPLIT you need to provide the text and the delimiter. The delimiter needs to be in quote marks. There are also a number of optional inputs.
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
You can see that we have first and last name together in column H. We have used TEXTSPLIT with space, written as ” “. Note that there are two spaces between BJ and Watling, so this has created an empty cell in the result. You can deal with this by using the optional argument ignore_empty which will ignore consecutive delimiters.
Note also, that the result spills into multiple cells, designated by the blue border around Ajaz and Patel.
This formula replaces some of what the text to columns button (found in the data menu) can do, but if you find that the formula, isn’t able to do what you want, then check out text to columns, it might be the answer you are looking for.
There is a lot more in the optional inputs that you can do, find out more about them here.
TEXTBEFORE
TEXTBEFORE is similar to TEXTSPLIT but it only does the text before the delimiter. Again, you need to input the text and the delimiter and there are a number of optional inputs.
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
This formula does not spill. It just brings back one cell of data.
TEXTAFTER
TEXTAFTER requires the same inputs as TEXTBEFORE but does the text after the delimiter.
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Three new text formulas to make your Excel spreadsheet more efficient. It is also worth noting that you can achieve similar results with flash fill, so if you are not into formulas and have a pattern that flash fill can understand, that might be a good option.