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:
- Right-click on Customer Zip Code from Dimensions, and select Create Calculated Field.
- In the Name box, type
Zip Region. - From the Functions dropdown, select String, as shown in the following screenshot:
- Find the
LEFTfunction and double-click on it. - Adjust the formula in the Formula box to
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:
- To extract the customer's last name, right-click on Customer from Dimensions and select Create Calculated Field.
- In the Name box, enter
Customer Last Name. - In the Formula box, enter
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.