Answer

Option 1: Using Tableau Prep and Tableau Desktop

Step 1 - Clean the data in Tableau Prep

1. Open Tableau Prep and connect to the original data source
2. Hover over Original Data, click the plus, and select Add Step
3. In Clean 1, right-click the field containing the list of values, this examples uses [Pet Name], and select Split Values > Custom Split…
4. In the Custom Split dialog, do the following and click Split
   • Enter the separator, which is ,
   • For Split off, select All
5. Hover over Clean 1, click the plus, and select Add Pivot
6. Drag [Pet Name - Split 1], [Pet Name - Split 2], and [Pet Name - Split 3] to the Pivot1 Values column
7. Hover over Pivot 1, click the plus, and select Add Output
8. In Output, run the flow

Step 2 - Build the view as normal in Tableau Desktop

Option 2: Create word cloud only using Tableau Desktop

Step 1 - Split the strings

1. Connect to the "Original Data" sheet of the attached Excel file
2. In the Data Source tab, right-click [Pet Names] and select Custom Split…
3. In the Custom Split dialog, do the following and click OK:
   1. For Use the separator, enter "," without quotes
   2. For Split off, select All

Step 2 - Create a duplicate of the data source for each split field

1. Navigate to a worksheet
2. Navigate to Data > Original Data (string to split) > Duplicate
3. Repeat step 5

Step 3 - Join master pet list

1. Connect to the "Master List" sheet of the attached Excel file
2. Navigate to Data > Edit Relationships…
3. In the Relationships dialog, so the following
   1. For Primary data source, select Master List (string to split)
   2. For Secondary data source, select Original Data (string to split)
   3. Select the Custom radio button
   4. Click Add…
   5. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 1, and click OK
   6. For Secondary data source, select Original Data (string to split) (copy)
   7. Select the Custom radio button
   8. Click Add…
   9. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 2, and click OK
   10. For Secondary data source, select Original Data (string to split) (copy 2)
   11. Select the Custom radio button
   12. Click Add…
   13. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 3, and click OK

Step 4 - Create a calculation and build the view

1. Create a calculated field with a name like "Word Size" with a calculation similar to the following:

   	ZN(SUM([Original Data (string to split)].[Number of Records]) )
   	ZN(SUM([Original Data (string to split) (copy 2)].[Number of Records]))
   	ZN(SUM([Original Data (string to split) (copy)].[Number of Records]))

2. Drag [Pet Names Master] to Text on the Marks card
3. Drag [Word Size] to Size on the Marks card
4. In the dropdown on the Marks card, select Text

Option 3: Create an average only using Tableau Desktop

Step 1 - Split the strings

1. Connect to the "Original Data" sheet of the attached Excel file
2. In the Data Source tab, right-click [Pet Age] and select Custom Split…
3. In the Custom Split dialog, do the following and click OK:
   1. For Use the separator, enter "," without quotes
   2. For Split off, select All

Step 2 - Create a calculation and build the view

1. Right-click [Pet Age - Split 1] in the data pane and select Change Data Type > Number (whole)
2. Right-click [Pet Age - Split 1] in the data pane and select Convert to Measure
3. Repeat steps 1-2 for all split fields
4. Create a calculated field with a name like "Avg Pet Age per Household" with a calculation similar to the following:

   SUM( ZN([Pet Age - Split 1])
   + ZN([Pet Age - Split 2])
   + ZN([Pet Age - Split 3]) )
   /
   ( COUNT([Pet Age - Split 1])
   + COUNT([Pet Age - Split 2])
   + COUNT([Pet Age - Split 3]) )

5. Drag [Avg Pet Age per Household] to Text on the Marks card
6. Drag [Household ID] to the Rows shelf

Additional Information