Count Occurrences of Text in Excel with TEXTSPLIT and COUNTA #shorts
Excel
Jan 10, 2023 11:30 PM

Count Occurrences of Text in Excel with TEXTSPLIT and COUNTA #shorts

by HubSite 365 about Leila Gharani [MVP]

Pro UserExcel

In this video you'll learn how to use Microsoft Excel's TEXTSPLIT function together with the COUNTA function to count occurrences of text

In Microsoft Excel, you can use the TEXTSPLIT and COUNTA functions to count the number of occurrences of a specific piece of text within a cell or range of cells.

The TEXTSPLIT function is a new function introduced in Excel 365 that allows you to split a cell or range of cells into separate pieces based on a delimiter. For example, if you have a cell with the text "apple, banana, cherry" and you want to split the cell into separate pieces based on the comma delimiter, you can use the TEXTSPLIT function like this:

=TEXTSPLIT(A1, ",")

This will return an array of values: "apple", "banana", "cherry"

You can then use the COUNTA function to count the number of items in the array. The COUNTA function counts the number of non-blank cells in a range, so it is useful for counting the number of items in an array like this:

=COUNTA(TEXTSPLIT(A1, ","))

This will return the value 3, indicating that there are three items in the array.

You can use these functions together to count the number of occurrences of a specific piece of text within a cell or range of cells. For example, if you want to count the number of times the word "apple" appears in a range of cells, you could use the following formula:

=COUNTA(TEXTSPLIT(A1:A10, "apple"))

This will count the number of cells in the range A1:A10 that contain the word "apple". You can adjust the range and the search text as needed to meet your specific requirements.