Forum Discussion
Splitting multiple choice data into separate columns
Let's say Referrals is in B1 and the headings Acupuncture, Chiropractor etc. in C1, D1, ...
Enter the following formula in C2:
=IF(ISNUMBER(SEARCH(C$1,$B2)),"x","")
Fill to the right, then down (or vice versa).
See attached sample workbook.
- Thanks Hans. I created a similar solution using a slightly different formula, by what I'm really trying to figure out is whether I can get around creating the columns manually and creating them automatically and then populating them via something like power query.
Keith_Walworth Please find two possible PQ solutions in the attached workbook.
One is not dynamic, as it will split the referrals at every comma based on what it found when I created the script. And it hard-codes the new column names. If the number of items on any of the rows increases next time, the extra items will be ignored and you would have to manually adjust the script. But most of the script was created by just clicking in the user interface (except for the one step where you add an "X", which requires some typing). And it's not very complicated to fix the step that makes the split.
The second solution is dynamic. I finds out how many items are included in each row, finds the highest number and splits the column into the exact number of columns needed. But, it requires some manipulation of the underlying M-code.
Dear Riny_van_Eekelen
I reach out to you to request your support !
As per my Excel sheet attached, could you help me with a formula that can allow me to splitting multiple choice in multiple colomns.
I can get result only for one colomn but i would like to get results for 2 or 3 colomns.
Thank you for your support.
I'll leave that for someone who knows PowerQuery.
