Excel Meets Tableau

6 min readJul 13, 2022

As a part of my internship and training, I was assigned a course on Maven Analytics called Excel Pro Tips by Chris Dutton. I took the course and found it interesting. While doing the course, I thought, can I do the same thing in Tableau? The answer was Yes.

As a practice and also wanting to learn, I researched for almost 2 days and finally got the solutions I was looking for. I created a Word Counter tool in Tableau.

Its creation involves 2 things:-

Creating Parameters for user-defined inputs.
Creating calculations for our operations.

Creating a Parameters

First, create a Parameter. I gave a name Write a Sentence. This is used for the user to enter the text. Choose Datatype as String and Allowable Values as All.

Our Parameter is created. Choose the option Type-in for the Parameter as the user will type the sentence.

Then, it is up to you. Create a Calculation named String and call the Parameter in it like this.

Or you use the Parameter directly. The output will be the same.

Creating Calculations

Text Length

For Length, we use the LEN function in Excel. It returns the length of a given text string as the number of characters as shown below.

In Tableau, we also have the LEN function which does the same thing. Create a Calculation Length and write the calculation as shown below and the length would be calculated.

It calculates characters and the blank spaces. So the length is 18 i.e. 15+3(Character + spaces).

Counting Words

To count words in a sentence, or get the answer as 1 for a simple word, There is another logic. Chris explains it well in the Maven Analytics course. Also, you can find the formula online too. The formula is as follows:-

=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1

To begin with, we use the SUBSTITUTE function to remove and displace all spaces in the cell with a vacant content string (“). Then we use the LEN function to restore the length of the string without spaces.

Next, we will subtract the string length without spaces from the absolute length of the string. The number of words in a cell will be equivalent to the number of spaces. We need to then add 1.

In Tableau, we don’t have the SUBSTITUTE function. Instead, we will use REPLACE function. It does the same job as that of SUBSTITUTE. Create a Calculation Count Words and write the calculation as shown below and words will be counted.

We get the Output as 4 which is correct.

Characters without spaces.

If you look closely, we can derive the formula and logic from the above calculation for finding characters without spaces. If we just write the formula as LEN(SUBSTITUTE(cell,” “,””))+1, we get the characters without spaces.

If you see, the length of the sentence should be 18 but here it is showing 15. Why? because we have excluded or removed the spaces. There are 3 spaces in the sentence. After removing the spaces, we get 15 i.e. the original length of the text which is 18 minus the total space which is 3.

In Tableau, we will do the same with REPLACE instead of SUBSTITUTE. Create a Calculation Character without Spaces and write the calculation shown below and then, characters without spaces will be calculated.

We get the same result in Tableau.

Total Uppercase Count

Total Uppercase means how many uppercase letters are there in the entered string. For example, I am a Boy. The count I should get is 1. How to do it in Excel?

I found a website called Extendoffice which explains how to do it. Here, they make use of ASCII codes for checking. The formula is

=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(ROW(INDIRECT(“65:90”))),””)))

After writing the formula, I get the answer as 2. Nice!

In Tableau, we will use Regular Expressions to achieve this. Tableau Visionary Diego Martinez R gives a tip to use it in the forums here. Great Tip Diego.

Create Calculation Upper Case and write the Calculation logic as shown below.

REGEXP_REPLACE returns a copy of the given string where the regular expression pattern is replaced by the replacement string. We can use LEN to count in combination with REGEXP_REPLACE. Read more about it here.

This is my Output and as we can see, I got 2 Uppercase Letters.

Total Lowercase Count

Total Lowercase means how many lowercase letters are there in the entered string. For example, I am a Boy. The count I should get is 10. We use the same logic as Uppercase and change the ASCII codes in the formula. It is as follows

=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(ROW(INDIRECT(“97:122”))),””)))

In Tableau, we will again use Regular Expressions to achieve this. Create Calculation Lower Case and write the Calculation logic as shown below.

We keep everything as it is. We will change A-Z to a-z for lowercase. Here is my output.

It gave me the correct output as 10 in Tableau.

Total Digits

Total Digits mean counting all numbers present in the text. For example, I am a Boy1214. The count I should get is 4 as there are 4 digits. The formula I found on excelhow, is as follows:-

=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,””),1,””),2,””),3,””),4,””),5,””),6,””),7,””),8,””),9,””))

I get the answer as 4 which is correct. Let’s do this in Tableau. We will use Regular Expressions like in Uppercase or Lowercase. Create a Calculation Number Count and write the Calculation as shown below.