What are Tableau String Functions?
In a scenario where you need to manipulate the string values in your dataset, Tableau provides a series of functions to convert string to a number, slice strings, find the string size, and convert to uppercase or lowercase. They are used to clean, modify and extract any values that are string values.
Here are some values provided by default in Tableau. In this example, the word size of each country in Europe is calculated.
The LEN() function is a string function in Tableau that is used to give the length of the string.
Table of contents
Key Takeaways
- String functions in Tableau are used to manipulate and analyze text data.
- Tableau String Functions enable tasks such as extracting, replacing, and formatting text within calculated fields.
- Functions like LEFT, RIGHT, MID, and SPLIT allow you to extract specific portions of a string.
- Formatting functions such as UPPER, LOWER, and TRIM help standardize text cases and remove extra spaces.
- Conditional functions like CONTAINS, STARTSWITH, and ENDSWITH check for specific text patterns within a string.
- Use REPLACE to substitute occurrences of a substring within a text field with another substring.
Types of Tableau String Functions
There are many String functions in Tableau. These functions have different functionalities which can be utilized by the user in a variety of ways. Go through these Tableau String Functions in detail below.
#1 – ASCII Function
This is one of the Tableau String Functions available by default. By using the ASCII function, you can find the ASCII Value of the first word in a given string value.
It can be used in Tableau for other features, as shown.
Step 1: Open a new workbook in Tableau by selecting “File” – “New.”
Step 2: The food consumption in Europe dataset is used for this example. To do so, drag and drop the dataset into the workbook.
You can also do the same by clicking on “New Data Source” (or pressing Ctrl+D) and selecting the file type of your choice. It will open the Files application, where you can select the file of your choice.
Step 3: Click on the first sheet to start working on the example.
Step 4: Create a calculated field to call the ASCII String function in Tableau. The ASCII function can also be used to calculate the first word to ASCII values.
Step 5: Name the calculated field and call the ASCII value.
Step 6: In the parentheses, drag and drop the feature “Country” to this.
Step 7: Then, drag and drop the calculated field in the Country table formed in the Worksheet.
These are the ASCII values of the first letter of every country in Europe.
#2 – CHAR Function
The CHAR function in Tableau is used to convert integers in ASCII code to their string values. It returns only one value and can be used to find the alphabet for a given number.
It can be used in Tableau for other features, as shown.
Step 1: To open a new Tableau Workbook, select “File” – “New.”
Step 2: Click on the arrow button next to “Search” and select “Create Calculated Field.”
Step 3: Name the calculated field and give an integer in the ASCII range.
Step 4: Drag and drop this onto the worksheet and see the result.
Step 5: In another example, see how you can use the CHAR function in datasets. The food consumption in Europe is used.
Step 6: Create a new calculated field to return the first letter of each country. It can be done by combining the ASCII and CHAR functions.
Step 7: Name the calculated field and call the ASCII value inside the CHAR function for the feature “Country.”
The CHAR function takes the ASCII value of the first word and converts it into a string. Now, you can display the first letters of all the countries.
#3 – CONTAINS Function
The Contains function in Tableau is used to check whether a given substring from the user exists or not. It is a Boolean function that returns True or False.
In this example, you can check if the string “land” is present in the countries of Europe. To do so, follow the steps below.
Step 1: Use the food consumption in Europe dataset for the countries in Europe.
Step 2: Drag and drop “Country” in the “Rows” section.
Step 3: Create a new calculated field to use the CONTAINS function.
Step 4: Edit the calculated field and use the “Country” feature with the substring in the following component as shown.
Step 5: Drag and drop this field into the “Rows” section.
The table below is the resulting table.
#4 – ENDSWITH Function
The ENDSWITH function is also a Boolean function to check if a given string ends with a substring given by the user. If yes, it returns “True,” else it returns “False.”
In this example, you can use the ENDSWITH function to check whether a given country ends with the letter/string of your choice.
Step 1: In a new workbook, drag and drop the dataset of your choice. The food-consumption in Europe dataset is used.
Step 2: Place the feature “Country” in the “Rows” section.
Step 3: Under the Data Tab, click on the “arrow” next to the “Filter” icon and select “Create Calculated Field.”
Step 4: Use the “Country” feature and the substring of your choice in the parentheses of the ENDSWITH function, as shown.
Step 5: Place the newly created calculated field in the “Rows” section.
It is the table.
#5 – FIND Function
The FIND function is used to find the index in which a substring is found in a string. It returns the value 0 if the substring does not exist. If it is a substring, the index of the first letter is returned.
Using the function, you can find the position of letters in different country names. To do so, you need to create a calculated field. In the same food-consumption in Europe dataset, the following steps can be followed.
Step 1: In the “Rows” section, drag and drop the “Country” feature.
Step 2: Create a new calculated field to call the FIND function.
Step 4: Call the FIND function with the “Country” feature and the substring of your choice.
Step 5: Apply the calculated field and place it in the “Rows” section. You will get the table below.
#6 – FINDNTH Function
The FINDNTH function is used to return the index of a character’s occurrence in a string. It is used to find the place where the first, second, or the ‘n’th occurrence of that character occurs in the string.
Using the function, you can find the position of letters of different occurrences in different country names. To do so, you need to create a calculated field. You can use the food-consumption in Europe Dataset to continue these steps.
Step 1: Use the “Country” feature in the “Rows” section.
Step 2: To use the FINDNTH function, create a new calculated field.
Step 4: In the calculated field, edit the name of the country and specify the ‘n’th occurrence of that word in that string.
Step 5: Place the newly created calculated field in the “Rows” and observe the table.
#7 – ISDATE Function
This function in Tableau is used to verify if a given string value is in a date format or not. It can be in any format, such as:
- MM-DD-YYYY
- DD-MM-YYYY
- YYYY-MM-DD
It returns “True” if you provide a date value, else it will return “False.”
In this example, to use the ISDATE function, you’ll need a workbook and a calculated field.
Step 1: Create a calculated field.
Step 2: Edit the name of the Calculated field and call the ISDATE function. Write a date in any of the formats mentioned earlier in it.
Step 3: Place the field in the worksheet, and it will return the answer.
This is where the output is shown.
#8 – LEFT Function
The function is used to return the string from the start till the index number that you have given. It takes the string value/function and an integer value.
Here, the Airbnb dataset is used to use the LEFT function on the “Property Type” function.
Step 1: A new calculated field is needed to use the LEFT function.
Step 2: Before editing the calculated field, place the “Property Type” in the “Rows” section.
Step 3: Edit the calculated field and use any integer within the size of the string.
Step 4: Apply the calculations on the table by dragging and dropping it. You can see the final table.
#9 – LEN Function
It is a string function in Tableau that finds the length of a string. It can be used to find the string size of the features or individual ones.
It can be used in various features. In this example, you can see how this function is used in the Airbnb dataset of Austin, Texas.
Step 1: To start with, in a new worksheet, place the “Property Type” in the “Rows” component.
Step 2: Generate a calculated field for this example.
Step 3: Change the name of the calculated field and call the function with the “Property Type” defined in parentheses.
Step 4: Place this calculated field in the “Rows.”
Observe the final table.
#10 – LOWER Function
This function returns all of the letters in lowercase in Tableau. Given an input string, it converts all letters to lowercase format.
The Airbnb dataset is used to understand this example.
Step 1: Set the “Property Type” in the “Rows” component.
Step 2: Then, create a new calculated field for this example.
Step 3: Change the name of the calculated field and call Lower with the “Property Type” defined in parentheses.
Step 4: Place this calculated field in the “Rows” and observe the final table.
#11 – UPPER Function
Given an input string, the string function converts all letters to uppercase format.
We use the Airbnb dataset to understand this example.
Step 1: Start the process by placing the “Property Type” in the “Rows” component.
Step 2: Create a new calculated field to convert strings to uppercase.
Step 3: Edit the name and call the UPPER function to do so.
Step 4: Place this calculated field in the “Rows.”
This is the final table.
#12 – STARTSWITH Function
The STARTSWITH function is a Boolean function that checks if a given string starts with a substring given by the user. If yes, it returns “True”. Else, it returns “False.”
In this example, you can use the STARTSWITH function to check whether a given country ends with the letter/string of your choice. Note that this is case-sensitive, so if the first letter is in uppercase, use it the same way.
Step 1: In a new workbook, drag and drop the dataset of your choice. The Airbnb dataset of Austin, Texas, is used.
Step 2: Place the feature “Property Type” in the “Rows” section.
Step 3: Now start the process by creating a new calculated field in this workbook.
Step 4: Call the STARTSWITH function with the substring of your choice.
Step 5: Place the newly created calculated field in the “Rows” section.
This is the final table.
Important Things To Note
- Use TRIM to remove leading and trailing spaces, ensuring your data is clean and consistent.
- Use UPPER and LOWER to standardize the case of text data.
- Combine LEFT, MID, and RIGHT to extract specific parts of a string.
- Combine IF statements with Tableau string functions for conditional transformations.
- Avoid using complex string calculations directly in filters, as it can slow down performance.
- Use Tableau ZN or conditional logic to manage null values in your string calculations.
Frequently Asked Questions (FAQs)
Yes, you can combine multiple Tableau string functions to introduce all sorts of functionalities. For example, you can combine the CHAR and ASCII functions to get the first letter of each string.
Yes, there are limitations to using Tableau string functions:
• Performance Impact: Complex string manipulations can slow down large data sets.
• Case Sensitivity: Some functions are case-sensitive, requiring additional handling.
• Data Type Constraints: Tableau String functions are not suitable for numeric or date fields.
• Locale Differences: Behavior may vary across different locales and languages.
Yes, you can use integer values for certain Tableau String Functions. For example,
• CHAR accepts an integer as an argument.
• FIND and FINDNTH use integers as arguments.
• ISDATE uses date values.
• LEFT and RIGHT accept integer values to slice a given string to a substring starting from the left or right, respectively.
Yes, you can use string functions in Tableau Prep for data preparation. For example, you can use string functions to clean, format, and manipulate text data during the data preparation process. These can be applied as shown:
• Transform Step: Add a “Clean” or “Calculated Field” step to use string functions.
• Aggregations: Use Tableau String Functions to group and clean data before aggregating.
• Custom Calculations: Create calculated fields that use string functions for more advanced text manipulation.
Download Template
This article must be helpful to understand the Tableau String Functions, with its formula and examples.
Recommended Articles
Guide to What is the Tableau String Functions. We explain different types of string function including ASCII, CHAR, Contains, ENDSWITH, etc. with examples, and points to note. You can learn more from the following articles –
Leave a Reply