Excel Offers New Text Manipulation Tools
in 0by

Excel Offers New Text Manipulation Tools

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

Excel

TEXTBEFORE

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.

Using TEXTAFTER To Extract All The Data After A Specific Character
Figure 1 - Using TEXTBEFORE To Extract Data From A List

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.

Using TEXTBEFORE To Extract Data Beginning From The Right Side Of A List
Figure 2 - Using TEXTBEFORE To Extract Data Beginning From The Right Side Of A List

TEXTAFTER

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.

TEXTAFTER EXAMPLE

Using TEXTBEFORE To Extract Data From A List
Figure 3 - Using TEXTAFTER To Extract All The Data After A Specific Character

TEXTSPLIT

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.

TEXTSPLIT Example

TEXTSPLIT Formula Example
Figure 4 - TEXTSPLIT Formula Example

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.

SUMMARY

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.

Facebook
Twitter
LinkedIn

For more information on these and other Excel functions, please consider participating in a K2 Enterprises training class.

tommy@k2e.com

Tommy is one of the shareholders in K2 Enterprises (www.k2e.com), affiliating with the Firm in 2003 and joining as a shareholder in 2007. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. You may reach him at tommy@k2e.com.