String function in Tableau come in handy when dealing with text-based data, allowing you to easily manipulate and alter your text fields. Whether you’re working with customer names, product details, or categorizing data, these functions help you clean, split, join, replace, and format your text for better analysis. They’re especially useful for datasets with categorical data like names or descriptions, helping you ensure that everything is well-formed and displayed properly.
These string functions allow you to manage and format textual data within Tableau without relying on external tools. From cleaning customer information to processing product details, they help you prepare your text data for presentation for dashboards and reports.
String Function in Tableau
LEN()
Counts the number of characters in a string.
Scenario: Useful for checking if the length of a text field like a customer ID or phone number is correct.
Example: LEN(‘Tableau’) gives you 7.
LEFT()
Extracts characters from the left part of a string.
Scenario: Take a specific part of the text, like extracting the area code from a phone number.
Example: LEFT(‘Tableau’, 3) returns tab.
RIGHT()
Extracts characters from the right part of a string.
Scenario: Great for extracting suffixes or domain names.
Example: RIGHT(‘example.com’, 3) results in com.
MID()
Extracts substrings starting at a specific place.
Scenario: Use this to extract a part of a string, like a product code from a longer text.
Example: MID(‘Tableau Software’, 9, 8) gives you the software.
UPPER() and LOWER()
Converts text to all uppercase or lowercase.
Scenario: Standardize your text fields, like making sure all emails are in lowercase.
Example: UPPER(‘tableau’) results in TABLEAU.
TRIM()
Removes spaces from both sides of a string.
Scenario: Clean up text fields, especially those with extra spaces around names or product descriptions.
Example: TRIM(‘ Tableau ‘) results in Tableau.
CONCAT()
Joins two or more strings together.
Scenario: Combine fields such as first and last names, or merge addresses into one row.
Example: CONCAT(‘Tableau’, ‘ Software’) results in Tableau Software.
FIND()
Returns the position of a substring within a string.
Scenario: Use this to search for specific text, such as finding the “@” symbol in an email address.
Example: FIND(‘abc@xyz.com’, ‘@’) returns 4.
REPLACE()
Replaces one substring with another substring.
Scenario: Use this to clear out misspelled words or outdated information in a text field.
Example: REPLACE(‘abc@xyz.com’, ‘@’, ‘ [at] ‘) results in abc [at] xyz.com.
SPLIT()
Splits a string into parts based on a delimiter.
Scenario: Split an email address or full name into separate pieces.
Example: SPLIT(‘John.Doe@example.com’, ‘.’, 1) results in John.
ASCII()
Returns the ASCII code of the first character in a string.
Scenario: Mainly useful when handling coded or non-printable characters.
Example: ASCII(‘A’) returns 65.
SPACE()
Generates a string of spaces of the specified length.
Scenario: Useful for formatting reports where text alignment is important.
Example: SPACE(3) produces three spaces.
Why string functions are important
Cleaning and preparing data
Functions like TRIM() and UPPER() make it easy to clean up your text, ensuring that everything from customer names to product descriptions is consistent.
Combining and splitting data
CONCAT() and SPLIT() help you organize data the way you need it. Whether you’re merging fields like names and addresses or splitting them into parts, these functions save you time.
Custom formatting
Functions like CONCAT() let you create more informative tooltips by combining different fields, ensuring your data looks just right.
Improve data quality
Use FIND() and REPLACE() to detect and fix any errors or inconsistencies in your text, ensuring the data you’re working with is clean and reliable.
Automate text tasks
String functions like LEFT() and RIGHT() help you automate repetitive tasks. For example, it becomes easy to extract product IDs or parts of customer data, without manual editing.
Handling unstructured data
When working with datasets containing unstructured data, such as customer reviews or survey responses, functions like SPLIT() and FIND() help you extract information from text-heavy fields.
Steps to perform several String functions
Step 1: Open Tableau
Step 2: Click on Text file to connect with Tableau
Step 3: Browse csv file (or other file format as your need) and click on Open
Step 4: Data has loaded. Now take a look at the data for further action/processing.
LEN Function
Step 5: Right click over Comment and select Create Calculated Field… to create a field for storing the length of comment
Step 6: Specify a name (LengthOfComment) for field and fill the following:
Output for LEN Function
Note:
- It returned length of comment.
LEFT Function
Step 8: Right click over Comment and select Create Calculated Field… to create a field for storing first n characters from Comment
Step 9: Specify a name (First 15 character comment) for field and fill the following:
Output for LEFT Function:
Note:
- It returned first 15 character (from left) of comment.
RIGHT Function
Step 11: Right click over ItemID and select Create Calculated Field… to create a field for storing last n characters from ItemID
Step 12: Specify a name (ItemShortID) for field and fill the following:
Output for RIGHT Function:
Note:
- It returned last 2 character (from right) of ItemID.
UPPER Function
Step 14: Right click over Customer and select Create Calculated Field… to create a field for storing upper case of customer name
Step 15: Specify a name (CustomerInUpperCase) for field and fill the following:
Output for UPPER Function:
Note:
- It returned customer name in upper case.
ASCII Function
Step 14: Right click over Customer and select Create Calculated Field… to create a field for storing ASCII value of 1st character of name
Step 18: Specify a name (ASCII-Of-Name) for field and fill the following:
Output for ASCII function:
Note:
- It returned ASCII value of first character of customer name.
Conclusion
String functions in Tableau are a must when working with text-based data. Whether it’s cleaning up text fields, combining different data points, or splitting text into meaningful segments, these functions help ensure that your data is clean, accurate, and ready for visualization.
From minor tasks like removing extra spaces to more complex tasks like replacing or formatting text fields, string functions provide everything you need to effectively handle and present your textual data.
If you like the article and would like to support me, make sure to:
🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
👏 Like for this article and subscribe to our newsletter
📰 View more content on my DataSpoof website
