Excel Offers New Text Manipulation Tools
Microsoft 365 subscribers can take advantage of three new text manipulation tools in Excel to assist them in manipulating text entries in their spreadsheets. These functions – TEXTBEFORE, TEXTAFTER, and TEXTSPLIT – are now available to all Excel users running subscription versions 2208 or newer in a Windows environment and 16.65 on a Mac. All three tools will go a long way toward helping you work with text in your Excel spreadsheets more effectively than with other Excel functions or techniques
You can use Excel’s TEXTBEFORE function to identify and return all text that occurs before a specific character or string of text. Following is the general syntax for a formula that uses TEXTBEFORE:
=TEXTBEFORE(text, delimiter, instance_num, match_mode, match_end, if_not_found)
- Text is the text string you are searching in. It is a required entry, and wildcard characters are not allowed. If text is an empty string, the formula returns an empty string.
- Delimiter is the text that marks the point before which you want to extract text. It, too, is a required field.
- Instance_num is the instance of the delimiter after which you want to extract text, with a default entry of 1. If the instance_num argument is negative, TEXTBEFORE starts searching from the end of the text. Intance_num is an optional argument in a TEXTBEFORE formula.
- Match_mode is an optional argument that determines whether the search is case-sensitive. By default, TEXTBEFORE is case-sensitive. To search without case sensitivity, enter a value of “1” for the match mode.
- Match_end is an optional argument that treats the end of the text as a delimiter. If you enter “0”, TEXTBEFORE does not match the delimiter against the text string’s end. If you enter “1”, TEXTBEFORE matches the delimiter against the text string’s end.
- If_not_found is an optional argument that indicates the value Excel should return if it finds no match. By default, Excel returns #N/A if it does not find a match.
TWO TEXTBEFORE EXAMPLES
To illustrate the power and ease of use of TEXTBEFORE, consider the following two examples. In the first example, the simple formula of =TEXTBEFORE(B2,”,”) is entered into cell A2 of the worksheet and copied through cell A13. The formula extracts the text in cells B2 through B13 up to the point of the first comma in each cell. It then returns the text to the corresponding row in column A.
In the example pictured in Figure 2, the optional argument of “-1” added to the formula forces TEXTBEFORE to operate from right to left. Notice that in this case, the results do not include the postal code.
Excel’s TEXTAFTER function is similar to the TEXTBEFORE function. However, as its time implies, TEXTAFTER returns the data after the delimiter instead of before the delimiter. The syntax for TEXTAFTER is the same as that for TEXTBEFORE; therefore, if you know how to use either of these functions, you should also be able to use the other one easily.
Following is the general syntax for a formula that uses TEXTSPLIT.
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
- Text is the text string you want to split and is a required entry.
- Col_delimiter is the text that marks the point where to spill the text across columns.
- Row_delimiter is the optional text that marks where to spill the text down rows.
- Ignore_empty is an optional argument. By default, the argument defaults to FALSE, which creates an empty cell. However, you can specify TRUE to ignore consecutive delimiters.
- Match_mode is an optional argument that defaults to 0 to perform a case-sensitive match. You can specify 1 to perform a case-insensitive match.
- Pad_with is the value with which to pad the result. The default is #N/A.
And, as mentioned previously, unlike Flash Fill or the Text To Columns Wizard, with TEXTSPLIT formulas in place, if you change the underlying data in column B, the results in columns C through G update automatically.
If you run a subscription version of Excel (2208 or newer in Windows or 16.65 on a Mac), you have access to three text manipulation tools that can help you manipulate text in your spreadsheets. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT all provide excellent functionality when you need to work with text in your Excel spreadsheets. Further, because you will use these functions in formulas, changes to underlying data will cause the formula results to update automatically, allowing you to work more efficiently in Excel. Therefore, give these tools a try as they become available to you. They are easy to work with and pay terrific productivity dividends.
For more information on these and other Excel functions, please consider participating in a K2 Enterprises training class.