Here, we will learn how to use the Excel dynamic array function: TEXTSPLIT. Later, we will also learn how to find average, sum, max, min and count using TEXTSPLIT with the AVERAGE, SUM, MAX, MIN & COUNT functions.
TEXTSPLIT function
The Excel TEXTSPLIT function splits text by the given delimiter to an array that spills into multiple cells automatically. Let's take an example. Assume the value in A1 as "10,20". To split the text "10,20" into separate values in columns, use the formula like so: =TEXTSPLIT(A1,","). Let's take another example. The formula =TEXTSPLIT("a-b-c","-") splits the text "a-b-c" into separate values in columns using the "-" delimiter. The function always returns the result in the text format even if the return value is a numeric value.
TEXTSPLIT takes six arguments: text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]. The first required argument text takes the text to split. Generally, a cell reference containing the text to split is used in the formula. The second required argument col_delimiter takes a character(s) that indicates where to split the text across columns. If this argument is omitted, row_delimiter must be used. The third optional argument row_delimiter takes a character(s) that indicates where to split the text across rows. The fourth optional argument ignore_empty specifies whether to ignore empty values or not: FALSE is the default value that creates empty cells for consecutive delimiters; TRUE does not creates empty cells for two or more consecutive delimiters. The fifth optional argument match_mode determines case-sensitivity for the given delimiter. 0 is default and is case-sensitive. 1 is case-insensitive. The final optional argument pad_with takes the value to use in place of missing values in 2d arrays. If omitted, #N/A error is used.
Finding AVERAGE, SUM, MAX, MIN & COUNT using TEXTSPLIT
To find average, sum, max, min and count of numbers, use TEXTSPLIT and nest it in the AVERAGE, SUM, MAX, MIN & COUNT functions separately. Do so only if your dataset contains numbers containing a delimiter.
The dataset (shown in the image) contains numbers in column A, containing the comma delimiter ",". The goal is to find the average, sum, max, min and count of numbers in separate five columns. Since the numbers in column A are not separate, using the AVERAGE, SUM, MAX, MIN or COUNT function will not give the desired result. To accomplish the task, we need to first separate the numbers using TEXTSPLIT and then use the required functions.
The formula in B3, C3, D3, E3 and F3 respectively, copied down, is:
=AVERAGE(--TEXTSPLIT(A3,","))
=SUM(--TEXTSPLIT(A3,","))
=MAX(--TEXTSPLIT(A3,","))
=MIN(--TEXTSPLIT(A3,","))
=COUNT(--TEXTSPLIT(A3,","))
How this formula works
In each formula, TEXTSPLIT splits the text "26,32,74" into separate values into columns using the col_delimiter ",". As said earlier, TEXTSPLIT returns the result in the text format so, TEXTSPLIT returns the numeric values in the text format. To convert those numeric text values into numeric values, insert the double negative operator (--) in front of the TEXTSPLIT formula. After this, supply the entire TEXTSPLIT formula to AVERAGE, SUM, MAX, MIN and COUNT separately that finds the average, sum, max, min and count of numbers respectively.
You have successfully learnt how to use the Excel dynamic array function: TEXTSPLIT, and how to find average, sum, max, min and count using TEXTSPLIT with the AVERAGE, SUM, MAX, MIN & COUNT functions! I hope this post helped you.
