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

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.

• TEXTSPLIT at Microsoft
  https://support.microsoft.com/de-de/office/textsplit-funktion-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7
• Use COUNTA to count cells that aren't blank
  https://support.microsoft.com/en-us/office/use-counta-to-count-cells-that-aren-t-blank-a4fd3d22-4a47-4c3e-b2aa-40e6394315c7

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.