Tableau String Functions

Tableau provides various string Functions to format or alter string data or substring. They are Tableau LEN, LTRIM, RTRIM, TRIM, CONTAINS, MID, REPLACE, etc. In this article, we will show you how to use Tableau String Functions with examples.

To demonstrate these Tableau String Functions, we are going to use the below-shown data. As you can see, there are 15 records in this table.

Remember, this is an Excel Worksheet. So, Please refer Connecting to the Excel Files article to understand the connection settings.

Tableau String Functions 1

Tableau String Functions

The subsequent examples will show you the list of String Functions in Tableau

LEN

The Tableau LEN function returns the length of a string and the syntax of this LEN is:

LEN(string)

To demonstrate the string functions, we have to use Calculated Fields. To create a calculated field, please navigate to Analysis Tab and select Create Calculated Field… option.

Tableau String Functions 2

Selecting the Create Calculated Field… option will open the following window. Here, we renamed the default calculation name as LEN.

The below LEN statement will find the length of each record in Department column

LEN([Department])
LEN Function 3

Let me add this field to the existing table. Please refer Create Table Report article to understand the process of creating a table

LEN Function 4

ASCII

The Tableau ASCII function will find the ASCII value of a character. If you pass more than one character, then it will return the ASCII value of the first character. The syntax of this ASCII is:

ASCII(text)

Below statement will find the ASCII values of the records present in Last name column

ASCII([LastName])

Let me add this field to the Rows. From the below screenshot, you can see, it is returning the ASCII value of the first character.

ASCII Function 5

CHAR

The Tableau CHAR function will convert or return the character for the given ASCII value and the syntax of this CHAR is:

CHAR(number)

For example,

CHAR(ASCII([LastName]))

Let me add this field to a Row shelf. From the below screenshot, you can see, it is returning the ASCII value of the first character.

CHAR Function 6

CONTAINS

The Tableau CONTAINS function returns True if the specified substring is present in the original; otherwise, it returns false. The syntax of this CONTAINS is:

CONTAINS(text, substring)

The below contains statement checks whether the records present in the Department column contains Software substring or not.

CONTAINS([Department],'Software')
contains Function 7

ENDSWITH

The Tableau ENDSWITH function returns True if the original text ends with specified substring; otherwise, it returns false and the syntax of this is:

ENDSWITH(text, substring)

The below statement will check whether the records present in the Department column ends with rs or not.

ENDSWITH([Department],'rs')
endswith Function 8

STARTSWITH

Tableau STARTSWITH is one of the string functions that returns True if the original text starts with specified substring; otherwise, it returns false. The syntax of this STARTSWITH is:

STARTSWITH(text, substring)

The below startswith statement checks whether the Department column records start with S or not.

STARTSWITH([Department],'S')
startswith Function 14

FIND Function

The Tableau FIND function returns the index position of a substring within the original text. The syntax of this is:

FIND(text, substring)

The below find statement will find the index position of re in the Department column.

FIND([Department],'re')

Let me add this find field to Rows shelf.

find Function 9

The find method also allows you to use the third argument to specify the starting point.

FIND(text, substring, starting_Index_Position)

The below statement will find the index position of e in the Department column where the lookup with a start at index position 5.

FIND([Department],'e', 5)
find Function 10

FINDNTH

The Tableau FINDNTH function returns the index position of the nth occurrence of a substring within the original string. The syntax of this FINDNTH is:

FINDNTH(text, substring, Occurrence)

The following statement finds the index position of the second occurrence of e in the Department column.

FINDNTH([Department],'e', 5)
findnth Function 11

LEFT

The Tableau LEFT function returns the leftmost characters up to a specified index position and the syntax of this LEFT is:

LEFT(str, index_position)

The below code returns the first five characters from the Department column.

LEFT([Department], 5)

Let me add this LEFT field to Rows.

left Function 12

RIGHT

The Tableau RIGHT function returns the rightmost characters up to a specified index position and the syntax of this RIGHT is:

RIGHT(string, index_position)

The below Right function returns the last nine characters from the Department column.

RIGHT([Department], 9)

Let me add this Right field to the Rows.

right Function 13

UPPER

The Tableau UPPER function converts all the characters in a string to uppercase and the syntax of this UPPER is:

UPPER(string)

The below Upper statement will convert the records in the Occupation column to uppercase.

UPPER([Occupation])

Let me add this Upper field to the Rows shelf.

upper Function 15

LOWER

The Tableau LOWER function converts a given string to lowercase and the syntax of this LOWER is:

LOWER(string)

The below lower function converts the department column records to lowercase.

LOWER([Department])

Let me add this Lower field to Rows shelf.

lower Function 16

MID

The Tableau MID function, also called substring, returns a substring from the original string. The syntax of this MID or substring is:

MID(string, start_position, length)

This Tableau Substring function accepts three arguments:

  • Original String
  • Starting index position – Substring will start from this position
  • Length (optional) – This is the total length of a substring.

The below Mid statement will return substring starts at index position five from the Department column.

MID([Department], 5)

Let’s add this MID field to the Rows shelf.

mid Function 17

The below Tableau Mid function return substring starts at index position five and ends after the substring length reaches to 8.

MID([Department], 5, 8)
mid Function 18

REPLACE

The Tableau Replace function replaces a part of the original with a new string and the syntax of this Replace is:

REPLACE(original text, old_sub, new_sub)

This Replace function accepts three arguments:

  • Original
  • Old_sub – Part of a text that you want to find and replace
  • New_sub – This is the new word that will replace the Old_sub

The below Replace statement will replace the Software term in the Dept column with Web.

REPLACE([Department], 'Software', 'Web')

Let me add this Replace field to Rows shelf.

replace Function 19

SPACE

The Tableau Space function helps you to return empty spaces and the syntax of this Space method is:

SPACE(integer)

The below statement will add five empty spaces in between the First Name and Last name. By the way, we are concatenating two strings here!

[First Name] + SPACE(5) + [Last Name]

Let’s add this Space field to Rows shelf.

space Function 20

SPLIT

The Tableau Split function is one of the String functions. It replaces a substring from the original string using the token number and the sequence. The syntax of this Split Function is:

SPLIT(original str, Sequence, token_number)

The split string accepts three arguments:

  • Original
  • Sequence uses this sequence to split the original string. For example, Comma, space, etc.
  • token_number – Occurrence of the Sequence. For example, Sequence is a comma, and token_number is three means Sub-string will start writing from the character after the third comma and ends before 4th comma
SPLIT([Department], ' ', 2)

Let me add this Split field to the Rows shelf.

split Function 22

LTRIM

The LTRIM is one of the Tableau string functions that removes the empty spaces from the left side and the syntax of it is:

LTRIM(text)

It removes empty spaces from the left side of the Last Name

LTRIM(Spaces)

Let me add this LTRIM field to the Rows shelf.

ltrim Function 21

Let me create a New column by adding 10 extra space before the Last name, and 15 extra spaces after the last Name

SPACE(10) + [Last Name] + SPACE(15)
space Function 23

We will use this calculated field to explain the RTRIM, and TRIM.

RTRIM

The Tableau RTRIM function removes empty spaces from the right side of a string and the syntax is:

RTRIM(text)

Below RTRIM statement will remove empty spaces from the right side of a Spaces column

RTRIM(Spaces)
rtrim Function 24

TRIM

The Tableau Trim function is one the string function, which removes empty spaces from the left and right side and the syntax of this is:

TRIM(text)

The below Trim statement removes blank spaces from Spaces column

TRIM(Spaces)
trim Function 25