Here, we will learn how to use the Excel dynamic array functions: TEXTAFTER, TEXTBEFORE and TEXTSPLIT.
TEXTAFTER function
The Excel TEXTAFTER function returns the text that occurs after the given delimiter. For example, to extract the number 1 occurring after the delimiter "," from the text "A,1", use TEXTAFTER like this: =TEXTAFTER("A,1",","). The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with TEXTAFTER if you are working with numeric values, like this: =VALUE(TEXTAFTER("A,1",",")). The function can be found in Excel 365 - Excel 2024. TEXTAFTER takes the arguments like this: (text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]), of which text and delimiter are required. Text accepts the text string from which we want to extract a text. Delimiter accepts the character in the text that we want to extract the text after it. Instance_num accepts the occurrence number of the character in the text that we want to extract the text after it. Omitting the argument defaults to 1. Positive number counts from the start and negative number counts from the end. Match_mode is a case-sensitivity argument. TEXTAFTER is case-sensitive that means that it can recognize if a letter is a capital or a small letter. The argument accepts 0 for case-sensitive or 1 for case-insensitive. Omitting the argument defaults to 0. Match_end treats the end of text as delimiter. It accepts 0 to disable or 1 to enable. Omitting the argument defaults to 0. If_not_found accepts a custom value to use when no match is found. Omitting the argument will force the function to return the #N/A error.
TEXTBEFORE function
The Excel TEXTBEFORE function returns the text that occurs before the given delimiter. For example, to extract the number 1 occurring before the delimiter "," from the text "1,A", use TEXTBEFORE like this: =TEXTBEFORE("1,A",","). The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with TEXTBEFORE if you are working with numeric values, like this: =VALUE(TEXTBEFORE("1,A",",")). The function can be found in Excel 365 - Excel 2024. TEXTBEFORE takes the arguments like this: (text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]), of which text and delimiter are required. Text accepts the text string from which we want to extract a text. Delimiter accepts the character in the text that we want to extract the text before it. Instance_num accepts the occurrence number of the character in the text that we want to extract the text before it. Omitting the argument defaults to 1. Positive number counts from the start and negative number counts from the end. Match_mode is a case-sensitivity argument. TEXTBEFORE is case-sensitive that means that it can recognize if a letter is a capital or a small letter. The argument accepts 0 for case-sensitive or 1 for case-insensitive. Omitting the argument defaults to 0. Match_end treats the end of text as delimiter. It accepts 0 to disable or 1 to enable. Omitting the argument defaults to 0. If_not_found accepts a custom value to use when no match is found. Omitting the argument will force the function to return the #N/A error.
TEXTSPLIT function
The Excel TEXTSPLIT function splits text by the given delimiter across columns or/and rows. For example, to split the numbers 1, 2 and 3 across columns by the delimiter "," from the text {1,2,3}, use TEXTSPLIT like this: =TEXTSPLIT({1,2,3},","). The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with TEXTSPLIT if you are working with numeric values, like this: =VALUE(TEXTSPLIT({1,2,3},",")). The function can be found in Excel 365 - Excel 2024. TEXTSPLIT takes the arguments like this: (text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]), of which text is required and at least one value for col_delimiter or row_delimiter is required. Text accepts the text string which needs to be split across columns or/and rows. Col_delimiter accepts a character(s) indicating where to split the text across columns. If the argument is omitted, row_delimiter must be used that accepts a character(s) indicating where to split the text across rows. If the argument is omitted, col_delimiter must be used. Ignore_empty controls whether the function should ignore empty values or not. It is a Boolean argument that accepts FALSE to create empty cells for consecutive delimiters or TRUE to not create empty cells for two or more consecutive delimiters. Omitting the argument defaults to FALSE. Match_mode is a case-sensitivity argument. TEXTSPLIT is case-sensitive that means that it can recognize if a letter is a capital or a small letter. The argument accepts 0 for case-sensitive or 1 for case-insensitive. Omitting the argument defaults to 0. Pad_with accepts a custom value to use in place of missing values in two-dimensional arrays. Omitting the argument will force the function to return the #N/A error to use in place of missing values.
You have successfully learnt how to use the Excel dynamic array functions: TEXTAFTER, TEXTBEFORE and TEXTSPLIT.
