KNOWLEDGE BASE

Finding the Number of Words or Letters in a String


Published: 08 Jun 2018
Last Modified Date: 23 Aug 2018

Question

How to calculate the number of words or specific characters in a string.

For example,
  • How many words does the string "this is an example" contain?
  • In the word Mississipi; how many times do the letters "ss" appear?

Environment

Tableau Desktop

Answer

Example 1 - Number of words

Assuming the string contains no leading or trailing spaces, use the following calculation:
LEN([string]) - LEN(REPLACE( [string], ' ', ''))) + 1
Replace [string] with the name of the string field, and make sure to use the right aggregation. If there is only one value of [string] as in the example workbook, ATTR should be used instead of SUM. Assuming no leading or trailing spaces, which can be removed with the TRIM() function, there should always be one more word in a string containing a phrase than there are spaces.

For example, "this is an example" contains 4 words and 3 spaces. Using the below calculation will remove the spaces in the [string] field by replacing them with an empty string.
REPLACE( [string], ' ', '')

LEN() returns the length of a string. By removing the spaces from a string, and subtracting the new string's length from the original's length, we get the number of spaces. Then we need only add 1 to find the number of words:
LEN([string]) - LEN(REPLACE( [string], ' ', ''))) + 1

Additionally, the above examples assume that the strings contain a series of words separated by single spaces. If your strings have multiple spaces (see example below), you will first need to use a regular expression to replace any number of spaces with a single space: 

One      two                three

REGEXP_REPLACE([string], ‘ +’,’ ‘)
  

Example 2 - Number of characters

Here are steps demonstrated in the attached workbook. In the workbook, the number of "ss" is counted.
  1. Create a calculated field named as Col1 (Convert ss to |) which contains the following formula.
    REPLACE([Col1],"ss","|")
  1. Create a calculated field named as Number of ss which contains the following formula.
    LEN([Col1 (Convert ss to |)]) - LEN(REPLACE([Col1 (Convert ss to |)],"|",""))
Did this article resolve the issue?