Home / Tableau Tutorial / Day 5
Day 5 — Text functions and cleaning data
Splitting codes, standardising labels and pulling apart the fields you were given.
The functions worth knowing
Every calculation on this page runs against the Orders data source below - 48 rows, dimensions in blue, measures in green, exactly as Tableau colours them.
Splitting an ID apart
Order IDs here look like ORD-1001. The prefix and the number are two different facts stuck in one field.
SPLIT beats counting characters
RIGHT([Order ID], 4) works only while every number is four digits. SPLIT([Order ID], "-", 2) takes whatever follows the dash, however long it is. Whenever there is a separator, split on it rather than assuming a position.CONTAINS, STARTSWITH, ENDSWITH
CONTAINS finds "top" inside both "Laptop" and "Docking Hub"? Run it and see - this is exactly the trap of matching on fragments. Always check what a CONTAINS actually caught before you trust it.
Cleaning inconsistent text
TRIM is the first thing to try when a join or filter fails
Two values that look identical but will not match almost always differ by an invisible space. LEN tells you: if
LEN([Region]) is 6 for something that reads as five characters, there is a space hiding in there.Building a label
In Tableau the plus sign joins text as well as adding numbers - it decides which by looking at the types. Mixing a number into a text join needs STR() around the number.
Where this belongs
Clean once, as early as you can
A calculated field runs every time the view redraws. If a field always needs the same cleaning, do it in the data source or in Tableau Prep instead - the workbook stays faster and every worksheet gets the clean version.
Try these yourself
- Extract the numeric part of Order ID as a number.
- Find the length of the longest product name using MAX and LEN.
- Total the sales for products whose name contains "o".
- Build a label reading "Laptop - North".
- Explain why SPLIT is safer than RIGHT for pulling out an ID number.