String functions (LEFT, RIGHT, CONTAINS) in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to analyze customer data to identify customers from a specific city, extract area codes from phone numbers, and categorize customers based on their email domains.

📊 Data: You have a customer dataset with columns: CustomerID, CustomerName, City, PhoneNumber, and Email.

🎯 Deliverable: Create a Tableau dashboard showing: 1) A list of customers from the city 'Springfield', 2) Extracted area codes from phone numbers, and 3) A count of customers grouped by email domain categories.

Sample Data

CustomerID	CustomerName	City	PhoneNumber	Email
101	John Smith	Springfield	[phone]	[e-mail]
102	Mary Johnson	Rivertown	[phone]	[e-mail]
103	James Lee	Springfield	[phone]	[e-mail]
104	Patricia Brown	Lakeside	[phone]	[e-mail]
105	Michael Davis	Springfield	[phone]	[e-mail]
106	Linda Wilson	Rivertown	[phone]	[e-mail]
107	Robert Garcia	Lakeside	[phone]	[e-mail]
108	Barbara Martinez	Springfield	[phone]	[e-mail]

1

Step 1: Filter the dataset to show only customers from the city 'Springfield'.

Create a filter on the 'City' field with condition: City = 'Springfield'.

Expected Result

Only customers with City = 'Springfield' are displayed: John Smith, James Lee, Michael Davis, Barbara Martinez.

2

Step 2: Create a calculated field to extract the area code from the PhoneNumber field.

Use the formula: MID([PhoneNumber], 2, 3)

Expected Result

Area codes extracted: '217' for Springfield customers, '312' for Rivertown, '415' for Lakeside.

3

Step 3: Create a calculated field to categorize customers by their email domain (e.g., gmail, yahoo, outlook, hotmail).

Use the formula: IF CONTAINS([Email], 'gmail.com') THEN 'Gmail' ELSEIF CONTAINS([Email], 'yahoo.com') THEN 'Yahoo' ELSEIF CONTAINS([Email], 'outlook.com') THEN 'Outlook' ELSEIF CONTAINS([Email], 'hotmail.com') THEN 'Hotmail' ELSE 'Other' END

Expected Result

Customers categorized by email domain correctly.

4

Step 4: Create a dashboard with three components: 1) Filtered customer list from Springfield, 2) Table showing CustomerName and extracted AreaCode, 3) Bar chart counting customers by Email Domain category.

Add the filtered customer list worksheet, the area code table worksheet, and the email domain bar chart worksheet to the dashboard.

Expected Result

Dashboard displays Springfield customers, their area codes, and email domain counts.

Final Result

---------------------------------------------
| Springfield Customers Dashboard            |
|-------------------------------------------|
| Customers from Springfield:                |
| - John Smith                              |
| - James Lee                              |
| - Michael Davis                          |
| - Barbara Martinez                       |
|                                           |
| Customer Area Codes:                       |
| Name           | Area Code                 |
|----------------|---------------------------|
| John Smith     | 217                       |
| James Lee      | 217                       |
| Michael Davis  | 217                       |
| Barbara Martinez| 217                      |
|                                           |
| Email Domain Counts:                       |
| Gmail: 3                                   |
| Yahoo: 2                                   |
| Outlook: 2                                 |
| Hotmail: 1                                 |
---------------------------------------------

✓Most Springfield customers have the area code 217.

✓Gmail is the most common email domain among customers.

✓The city filter helps focus analysis on a specific location.

Bonus Challenge

Create a calculated field that extracts the last 4 digits of the phone number using RIGHT function and display it in the dashboard.

Show Hint

Use the formula: RIGHT([PhoneNumber], 4) to get the last 4 digits.

Practice

1. What does the Tableau function LEFT('Tableau', 3) return?

easy

A. 'Tab'

B. 'bleau'

C. 'Table'

D. 'au'

Solution

1. Step 1: Understand LEFT function

   The LEFT function extracts characters from the start of a string. Here, it extracts 3 characters from 'Tableau'.

2. Step 2: Extract first 3 characters

   The first 3 characters of 'Tableau' are 'Tab'.

3. Final Answer:

   'Tab' -> Option A

4. Quick Check:

   LEFT('Tableau', 3) = 'Tab' [OK]

Hint: LEFT gets characters from the start of text [OK]

Common Mistakes:

• Confusing LEFT with RIGHT function
• Extracting more or fewer characters than specified
• Assuming it extracts from the end

2. Which of the following is the correct syntax to check if the string 'Data' contains the letter 'a' in Tableau?

easy

A. LEFT('Data', 'a')

B. CONTAINS('Data', 'a')

C. CONTAINS('a', 'Data')

D. RIGHT('Data', 'a')

Solution

1. Step 1: Understand CONTAINS syntax

   CONTAINS(text, substring) checks if substring exists in text. The first argument is the main text, second is what to find.

2. Step 2: Apply to given strings

   To check if 'Data' contains 'a', use CONTAINS('Data', 'a').

3. Final Answer:

   CONTAINS('Data', 'a') -> Option B

4. Quick Check:

   CONTAINS('Data', 'a') = TRUE [OK]

Hint: CONTAINS(text, substring) order matters: text first [OK]

Common Mistakes:

• Swapping the order of arguments in CONTAINS
• Using LEFT or RIGHT instead of CONTAINS
• Passing characters as numbers

3. What is the result of RIGHT('Analytics', 4) in Tableau?

medium

A. 'tics'

B. 'Anal'

C. 'ytic'

D. 'naly'

Solution

1. Step 1: Understand RIGHT function

   RIGHT extracts characters from the end of a string. Here, it extracts 4 characters from 'Analytics'.

2. Step 2: Extract last 4 characters

   The last 4 characters of 'Analytics' are 'tics'.

3. Final Answer:

   'tics' -> Option A

4. Quick Check:

   RIGHT('Analytics', 4) = 'tics' [OK]

Hint: RIGHT gets characters from the end of text [OK]

Common Mistakes:

• Extracting characters from the start instead of end
• Counting wrong number of characters
• Confusing with LEFT function

4. You wrote the formula CONTAINS('Business', Business) in Tableau but it gives an error. What is the problem?

medium

A. The first argument should be a number

B. CONTAINS cannot be used with text

C. LEFT function should be used instead

D. Missing quotes around 'Business' in second argument

Solution

1. Step 1: Check CONTAINS argument types

   CONTAINS requires both arguments to be text strings or fields. Here, second argument is missing quotes, so Tableau treats it as a field name.

2. Step 2: Fix by adding quotes

   Adding quotes around 'Business' in second argument makes it a string literal, fixing the error.

3. Final Answer:

   Missing quotes around 'Business' in second argument -> Option D

4. Quick Check:

   CONTAINS('Business', 'Business') works [OK]

Hint: Always quote text strings inside CONTAINS [OK]

Common Mistakes:

• Using unquoted text instead of string literals
• Confusing CONTAINS with LEFT or RIGHT
• Passing numbers instead of text

5. You want to create a calculated field in Tableau that returns TRUE if the first 2 letters of a product code are 'AB' and the code contains '123'. Which formula is correct?

hard

A. CONTAINS([Product Code], 'AB') AND LEFT([Product Code], 3) = '123'

B. RIGHT([Product Code], 2) = 'AB' OR CONTAINS('123', [Product Code])

C. LEFT([Product Code], 2) = 'AB' AND CONTAINS([Product Code], '123')

D. LEFT([Product Code], 2) = 'AB' AND CONTAINS('123', [Product Code])

Solution

1. Step 1: Check LEFT function usage

   LEFT([Product Code], 2) extracts first 2 letters. We compare it to 'AB' to check the start.

2. Step 2: Check CONTAINS usage

   CONTAINS([Product Code], '123') checks if '123' is anywhere in the code. The order is correct: main text first, substring second.

3. Step 3: Combine conditions with AND

   Both conditions must be true, so use AND.

4. Final Answer:

   LEFT([Product Code], 2) = 'AB' AND CONTAINS([Product Code], '123') -> Option C

5. Quick Check:

   Correct syntax and logic = LEFT([Product Code], 2) = 'AB' AND CONTAINS([Product Code], '123') [OK]

Hint: Use LEFT for start, CONTAINS for anywhere, combine with AND [OK]

Common Mistakes:

• Swapping LEFT and RIGHT functions
• Reversing arguments in CONTAINS
• Using OR instead of AND