At a glance
Excel has always had several functions that manipulate text. The problem with the existing text functions is that you need to combine them to perform many common tasks.
The new text functions are built to extract text based on identifying special text within text, known as delimiters.
Extracting text in Excel
It’s common to extract text from the beginning of a text string and the end of the text string.
Excel has existing functions called LEFT and RIGHT that allow you to extract text based on a character count that you supply.
If you want to extract the first four characters from a text string you can use =LEFT(A1,4). If the code you are extracting from has a fixed four digits at the start, then this function works well.
The problem arises when you have a code that uses a delimiter character to separate the sections of the code. For example, you might have a code like ABCD-1234-WXYX.
The hyphen, which is used to separate the three sections within the code, is an example of a delimiter.
If the number of characters between the hyphens are a variable length, then using the LEFT or RIGHT functions becomes more difficult.
The LEFT and RIGHT functions have to some extent been replaced by the TEXTBEFORE and TEXTAFTER functions.
These allow you to specify a delimiter character to control how many characters to extract.
TEXTAFTER function
This new function has six arguments but only the first two are required. The four other optional arguments default to the common requirements.
Syntax
TEXTAFTER(text,delimiter,[instance-num],[match_mode],[match_end],[if_not_found])
- text – the text to extract from, usually a cell reference
- delimiter – the character or text string that separates the entries within the text. Any text must be enclosed within quotation marks
- instance_num – optional – the instance number of the delimiter to use. If omitted, the default is 1 (the first delimiter). Entering 2 refers to the second delimiter. Entering a negative number starts from the right. For example, -1 would use the last delimiter and -2 would use the second last
- match_mode – optional – if omitted, defaults to a case sensitive match. Enter 1 to ignore case
- match_end – optional – if the delimiter is not found, the #N/A error is displayed. Enter 1 to use the end as a delimiter, which will display text (first argument).
- if_not_found – optional – this specifies what to return if the delimiter is not found. Any text must be enclosed within quotation marks
The TEXTBEFORE function has the same syntax as TEXTAFTER, but it extracts the text before the delimiter.
Example
The CELL function allows you to display the full path of a file. This includes the folder path, the file name, and the sheet name for the current file.
In Figure 1, the CELL function is in cell A2. The formulas used in column A are shown in column B.
Extracting the sheet name from the end of cell A2 would previously have required combining three separate functions.
The TEXTAFTER function in cell A3 makes it easy. It extracts all the text after the closing square bracket.
The TEXTBEFORE function in cell A4 simplifies extracting the folder path of the file. It extracts the text before the opening square bracket.
There is no function to extract text between delimiters.
You can combine TEXTBEFORE and TEXTAFTER functions as shown in cell A5, which extracts the file name from between the opening and closing square brackets.
TEXTBEFORE and TEXTAFTER are each designed to extract a single item from a text string. The TEXTSPLIT function has been added to split up a text string into multiple items based on a delimiter.
TEXTSPLIT
This function creates a dynamic array spill range by separating a text string based on a delimiter character or characters.
This could be used to separate people’s names and General Ledger or stock item codes that use delimiters.
Syntax
TEXTSPLIT(text,col_delimiter,[row_delimiter],[match_mode],[match_end],[if_not_found])
- text – the text to extract from, usually a cell reference
- col_delimiter – the character or string that separates the entries within the text. This delimiter creates a horizontal dynamic array. Any text must be enclosed within quotation marks
- row_delimiter – optional – the character or string that separates the entries within the text. This delimiter creates a vertical dynamic array. Any text must be enclosed within quotation marks
- ignore_empty – optional – if two or more delimiters are found together, this argument can output an empty cell (the default action if the argument is omitted). It will ignore empty cells if you enter 1
- match_mode – optional – if omitted, defaults to a case sensitive match. Enter 1 to ignore case
- pad_with – optional – this is used for two-dimensional arrays. You can specify what to return if the return cell has no entry or is not valid. Any text must be enclosed within quotation marks
The most common requirement is to use the first two arguments.
Example
In Figure 2, we have codes listed in column A with the hyphen as the delimiter. These have been separated into dynamic array spill ranges on the right using the TEXTSPLIT function.
The formula in cell B2 (refer to the Formula Bar in Figure 2) has been copied down the column.
Flexible example
Figure 3 shows how to use the TEXTSPLIT function to create a single input cell to enter multiple items.
On the left is a list of states and amounts. We want to enter the states to summarise in cell D2, separated by commas. TEXTSPLIT extracts those states into a spill range.
The SUMIFS function in cell E3 refers to that spill range and adds up the states listed. Only three formulas are required, as shown in Figure 4.
The # symbol is used to refer to a spill range. You refer to the top left cell of the spill range followed by the # symbol.
This can cause the formula that uses the # symbol to spill in the same direction as the spill range. The formula in cell E3 has spilled down.
In this case, the TEXTSPLIT is returning a vertical spill range because it used the row_delimiter, the third argument.
Adding another state in cell D2 (see Figure 5) automatically updates the summary.
These new functions offer easier text manipulation and opportunities to automate more tasks in Excel. The companion video will include more examples.
