Analyze SharePoint multiple values field in an Excel pivot table

larrybryant

New Member
Joined
Jul 23, 2002
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have a SharePoint list with a Choice field that will allow multiple values. I would like to be able to analyze the data in an Excel pivot table.

There are no problems exporting the SharePoint list to Excel, however when the Choice fields (that permit multiple values) are exported they are placed in a column with the multiple choices separated with ";#".

For Example:
Products Capabilities
Shovel Dig;#1;Bury;#2;Cover;#3
Rake Dig;#1;Cover;#3
Hammer Dig;#1;Hang;#4
Pitchfork Cover;#3

The functions are not mutually exclusive and a product may have one or many capabilities.

I am looking for the best method for normalizing the multiple-value fields so I can analyze the data using a pivot table.

Any suggestions would be GREATLY appreciated.

Thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi Larry

I'm curious if you ever found a solution to the above problem?

I'm have the exact same issue where i need to analyse a SharePoint list in a pivot table and the multiple choice values shows up with ";#" seperators.

Best regards
Stine
 
Upvote 0

Forum statistics

Threads
1,223,404
Messages
6,171,922
Members
452,433
Latest member
Woodchuck76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top