Tableau String Functions

Tableau provides various string functions to format or alter string data or substrings. 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 will use the data shown below. As you can see, there are 15 records in this table.

Remember, this is an Excel Worksheet. So, Please refer to the 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.

Tableau String LEN Function

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 the Analysis Tab and select the 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. For the remaining Functions in Tableau >> Click Here.

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

LEN([Department])
Tableau String LEN Function 3

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

Tableau LEN Function 4

Tableau String ASCII Function

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)

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

ASCII([LastName])

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

Tableau 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 screenshot below, you can see it is returning the ASCII value of the first character.

Tableau CHAR Function 6

Tableau String Contains Function

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 statement below checks whether the records present in the Department column contain the Software substring or not.

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

ENDSWITH

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

ENDSWITH(text, substring)

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

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

STARTSWITH

Tableau STARTSWITH is one of the string functions that returns True if the original text starts with a 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')
Tableau String startswith Function 14

Tableau String 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 the Rows shelf.

Tableau String find Function 9

The find method also lets you 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 starts at index position 5.

FIND([Department],'e', 5)
Tableau String 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)
Tableau 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.

Tableau String 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.

Tableau String right Function 13

Tableau String UPPER Function

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.

Tableau String 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.

Tableau String 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 – The substring will start from this position
  • Length (optional) – This is the total length of a substring.

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

MID([Department], 5)

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

Tableau String mid Function 17

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

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

Tableau String Replace Function

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.

Tableau String 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 statement below will add five empty spaces between the first name and the last name. By the way, we are concatenating two strings here!

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

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

Tableau String space Function 20

Tableau String Split Function

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
  • The sequence uses this sequence to split the original string. For example, commas, space, etc.
  • token_number – Occurrence of the Sequence. For example, Sequence is a comma, and token_number is three, which means the 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.

Tableau String 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 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.

Tableau String ltrim Function 21

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

SPACE(10) + [Last Name] + SPACE(15)
Tableau String 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)

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

RTRIM(Spaces)
Tableau String rtrim Function 24

Tableau string Trim function

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

TRIM(text)

The below Trim statement removes blank spaces from the Spaces column.

TRIM(Spaces)
Tableau String trim Function 25