At times, we are required to parse or manipulate text variables to get something meaningful out of those variables; for example, a Full Name field may contain both the first name and last name of a sales representative, but our reporting standards may require us to show two different columns for the first and last names. With Tableau's string operators, we can easily manipulate the text to meet our requirements.
Let's use the sample file Sample – Superstore Sales (Excel)
. Open a new worksheet and select Sample – Superstore Sales (Excel) as the data source.
Once the data is loaded on the worksheet, perform the following steps to create new string fields based on existing text variables:
Zip Region
.LEFT
function and double-click on it.LEFT([Customer Zip Code], 1)
and hit OK. We can use this newly generated field to create a map as shown in the following screenshot:Customer Last Name
.RIGHT([Customer], LEN([Customer]) - FIND([Customer], " "))
and hit OK.The LEFT
function extracts the specified number of characters from the start of the given string variable. In our recipe, we extracted the first character of the Customer Zip Code value. The RIGHT
function works similarly except that it extracts characters from the end of the given string variable. The FIND
function returns the position of the searched string within a string variable. To extract the customer's last name, we first found the position of the space between the customer's first and last names in the Customer field. Then we computed the number of characters between the space and the end of the string by subtracting the position of the space from the total number (found using the LEN
function) of characters in the Customer field.