How to Count Words in Google Sheets Cells Separated by Dashes

Описание: Learn how to accurately count the number of words in a range of Google Sheets cells separated by "-", while avoiding false counts due to empty cells.
---
This video is based on the question https://stackoverflow.com/q/64010302/ asked by the user 'stax' ( https://stackoverflow.com/u/14321192/ ) and on the answer https://stackoverflow.com/a/64010513/ provided by the user 'General Grievance' ( https://stackoverflow.com/u/4294399/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Count number of words in range of cells separated by "-" in google sheets

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Count Words in Google Sheets Cells Separated by Dashes

When working with Google Sheets, you might encounter a situation where you need to count the number of words in a range of cells, and these words are separated by a specific character, like a dash (-). This can be a common requirement for data analysis, reporting, or simply organizing your information. However, users often face challenges with formulas that yield incorrect counts, especially when handling empty cells. In this post, we will solve this issue step-by-step.

The Problem at Hand

A user recently encountered a problem with their Google Sheets formula that counts the words in a range of cells. Specifically, the initial formula looked like this:

[[See Video to Reveal this Text or Code Snippet]]

While it seemed to work under certain conditions, it had a few significant downsides:

Counting Empty Cells: If any cells in the specified range were empty, the formula still returned 1 instead of 0. This was misleading.

Limited Range Functionality: The formula worked for two ranges but failed when used with three ranges.

Let’s dive into a solution that addresses these issues effectively.

A Better Solution

To achieve accurate word counting from a range of cells separated by dashes, we can modify the formula. Here’s a simplified approach that includes error handling and improves the functionality for various ranges.

Step 1: Use TEXTJOIN

Instead of using CONCATENATE, which doesn’t manage empty cells well, we’ll utilize TEXTJOIN. This function allows us to specify a delimiter (in our case, a dash) and can ignore empty cells. Here's how to use it effectively:

Step 2: Implement IFERROR

We also need to ensure that our formula does not return an error when trying to split contents that might lead to an error condition. By using IFERROR, we can manage any unexpected situations gracefully. Here's the refined formula:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula:

TEXTJOIN(CHAR(45), 1, D16:G16): This part joins the selected cells (D16 through G16) using a dash as a separator, while ignoring empty cells (1 means ignore).

SPLIT(..., CHAR(45)): This splits the result back into individual elements wherever there is a dash.

IFERROR(...): This function checks for errors in the SPLIT operation and prevents errors from affecting the count.

COUNTA(...): Finally, this counts all non-empty elements returned by the SPLIT function, providing an accurate word count.

Conclusion

With this refined formula, you will now receive a precise count of words in a specified range of Google Sheets cells, even when they are separated by dashes. This approach also successfully handles empty cells, returning a count of zero when no valid words exist. If you have any further questions or need additional assistance with Google Sheets formulas, feel free to reach out in the comments below!