Brainstorming Analyse this : Pivot and MS Forms and Multiple choice answer in single cell

Maybe you should consider your method of recording the original data if you're ending up with stuff in a single cell that needs splitting out.

However I'm puzzled. I've never used MS Forms before, (always use Google Docs), but I've just tried a test and the MS form when opened in Excel does give all the responses in separate cells across a row hence I don't see your single cell 'problem'

Please clarify.

This is normal behavior in SharePoint MS Forms but only when you use Choice and enable Multiple options. When couple 100 employees fill in form that must be analyzed via Pivot it is painful. Of course each Form fill answers are in separate row but Mutliple Choice answers on question x all reside in single cell limited by ;

Note that you can have for example 48/50 questions and all are ok as one record in Excel table. Just for example 2/50 Multiple Choice answers that have many selected answers in single cell are issue when doing BI analysis.

One solution I see is:
- to copy all such a columns with multiple answers to separate sheet,
- use Power Query to separate column & run analytics on separate table

Counting different items in single cell with less hassle would be nice...

Still thinking about formula solution and of course last resorts VBA