How to Split "Select All That Apply" Answers into Columns in Excel
Every survey tool, CRM and web form exports a multiple-choice answer the same way: every option the person chose, crammed into one cell. In that shape the data cannot be counted, filtered, pivoted or charted — and "how many people chose Email?" has no reliable answer.
Last updated: October 2026
| Channel | Phone | Chat | ||
|---|---|---|---|---|
| Email; Phone | → | Yes | Yes | No |
| Chat | → | No | No | Yes |
| Phone; Chat; Email | → | Yes | Yes | Yes |
Why COUNTIF and Text to Columns both fail here
=COUNTIF(B:B,"Email")counts only the people who chose nothing but Email.=COUNTIF(B:B,"*Email*")over-counts: it also matches "Email newsletter" and "No email".- Text to Columns splits by position, so Email lands in column B for one respondent and column D for the next. The data is no more countable than before, only wider.
- The textbook fix is one column per option — an indicator, or "dummy", column. XLSTAT has it inside a paid statistics suite and Dedoose ships a macro for it; neither Ablebits nor Kutools has it at all.
The 1-click way (Tellsheet)
Multi-Select to Columns
Select your data and run Multi-Select to Columns. It reads the option list out of the column itself — nothing to type, so nothing to miss — and adds one column per option. The separator is detected automatically (semicolon, comma, pipe, slash or line break), spellings differing only in case or spacing are treated as one option, and the columns keep the order the answers appear in. Mark the chosen options with Yes/No, 1/0 for counting and models, or x/blank to read by eye.
Two details that matter
- The order is the order they appear. A rating scale — Never, Rarely, Sometimes, Often, Always — was put in a deliberate order, and sorting it alphabetically destroys the only thing that made it readable. Alphabetical is available; it is not the default.
- Once the columns are 1/0, SUM is your count.
=SUM(C2:C500)is the number of people who chose that option, and the whole table pivots and charts normally.
Frequently asked questions
Why can't I just COUNTIF?
Exact match misses combined answers; wildcard match over-counts similar ones.
Why not Text to Columns?
It splits by position, so the same option lands in different columns on different rows.
Do I type the option list?
No — it is read from the data, which is the only way to be sure none is missed.
Can I get 1 and 0?
Yes, along with Yes/No and x/blank.
Related Excel guides
Countable survey data, in one click
Multi-Select to Columns finds the options for you and writes one column each — with a preview before anything changes.
Get Tellsheet free See pricing