Sign in or start a free trial to avail of this feature.
8. Searching for Text in Tableau
Like Excel, Tableau has many similar functions for searching text strings. In this lesson, we will learn how to use CONTAINS(), STARTSWITH(), FIND() and much more.
To explore more Kubicle data literacy subjects, please refer to our full library.
Functions for searching text in Tableau
- CONTAINS(): Checks to see if a sub-string exists anywhere within a string of text
- STARTSWITH(): Checks to see if a sub-string exists at the beginning of a string of text
- ENDSWITH(): Checks to see if a sub-string exists at the end of a string of text
- LEFT(): Returns a discrete # of characters from the left of a string
- RIGHT(): Returns a discrete # of characters from the right of a string
- FIND(): Returns the position of a sub-string within a larger string
- FIND() contd: If the sub-string is not present, the function returns zero
In Tableau, we have many functions to help us manipulate text strings. Some of these functions are used to clean data, while others are used to analyze the text within our columns. It is the latter set of functions I want to examine in this lesson. If we look at the list of customer IDs in the customer ID column, we can see that the first three characters are either A10, A11, A12, A13, A14, A15, all the way down to A19.
What I'd like to do is to separate the customers into two groups. Those that begin with A10 and then all of the others. There are a couple of ways to do this. Let's start with an easy one called contains. Contains accepts a text field and checks to see if a specific sub string exists within each row. In this case, I'm going to check if the string contains A10. If it does, the function will return true. And if it doesn't, it will return false. So let's create a calculated field called customer group.
And the formula will simply be contains.
The string will be customer ID and the sub string will be A10.
And then I'll press okay. Let's now add this new calculated field to my table.
And as you can see, it's always false until we hit all the A10 customer IDs. Well contains works okay in this particular instance. We might want to find nicer labels than true, false. And to do this, I'm going to put my contains function in an if else statement. So let's jump back into the formula.
So I'll hit the dropdown and I'll go to edit.
And in here, I'll say if contains customer ID of A10 then return A10.
Else I'll say A1X.
And then end.
And when I press okay, I now have A10 and A1X.
Contains is not the only way to perform this particular task. We can also use a function called starts with. So let's jump back into my calculated field, and I'm gonna change the word contains with starts with.
And as you can see, starts with accepts the same arguments as contains. First the string and then the sub string.
And when I press okay, the formula still works. As you can imagine, ends with works the exact same way except from the other end of the string. Another alternative is to use the left function. This function accepts a number of characters and slices that number of characters from the left of the main string. And in this case, we want three characters. So again, I'll go back into my calculated field. And this time I'll remove starts with and write left.
Now left takes slightly different arguments. Again, it takes the string and then it takes the number of characters, which in this case is three. And I want to check if this is equal to A10.
And if it is, I'll return A10, otherwise I'll return A1X, then I'll press okay.
And as you can see, this still works.
Needless to say, the right function works the same way except from the right of the string rather than from the left. Now let's do it using the find function. The find function searches for a string of text within a string and returns its position if found. If the sub string is not found, find returns zero. Let's see this in action. So I'll go back. My calculated field, and I'll type find.
It'll accept the sub string, which is A10.
And I want to check that this is not equal to zero, which means A10 exists. And if it does exist within the string, then I'll return A10.
Otherwise I'll return A1X. I'll press okay. And again my formula continues to work.
As you can see, many of these functions are very easy to use in Tableau, and analyzing strings using these functions is pretty straightforward. In the next lesson, however, we'll examine more complicated text strings with more challenging tasks that will require some additional functions.