# Convert Excel data

I've made a survey in Sharepoint and want to convert the results into something useful in Excel 2003. My problem is the format the responses are in for some questions.

An example of two responses (=two rows) for one question (=one column) can look like this

alternative A;#alternative B;#unique freetext alternative
alternative A;#alternative C

So all alternatives the user selected are in the same column, separated by `;#`

What I would like to do is to convert this to

alternative A = 2
alternative B = 1
alternative c = 1
unique freetext alternative = 1

This way it'll be easy to convert to a graph.

How would I go about doing this with the least amount of manual job? There are about 60-70 rows and each row has about 15 - 20 columns with this problem.

I'd guess I would need to somehow count the alternatives, separated by ;#?

If all else fails, I guess I could handle this with a bunch of `IF` statements

-

This isn't the prettiest or seemingly most elegant, but it's simple and will work great:

1. Put all your results in a column.
3. Use a simple formula to search the results column for the value in the column header. (I used `SEARCH()` to perform case-insensitive searching per your question. You can also use `FIND()` if you want case-sensitive results.
4. Include `IFERROR()`1 in case the value is not found.
5. Add a totals row at the bottom.

For a one-off grunt search, this is a quick and easy way to get meaningful numbers from the concatenated fields.

1 Excel 2003 does not have the `IFERROR` worksheet function. A workaround is to use `IF(NOT(ISERROR(...)))`. In this case, the example formula from the screenshot should read `=IF(NOT(ISERROR(SEARCH(B\$1,\$A2)>0)),1,0)`

-
This ended up being the best option but I had to gather the freetext answers manually. One addition though, the formula doesnt work on Excel 2003 since IFERROR doesnt exists. I had to do: IF(NOT(ISERROR(SEARCH(B\$1,\$A2)>0)),1,0) – Fredrik Mar 1 '12 at 15:45
@Fredrik Oops, I didn't realize that. Glad you found a workaround and thanks for the heads-up. – Farray Mar 1 '12 at 17:18

The technique I'd use is to count compare the lengths of all the cells in the column against the lengths of all the cells with the specific response removed, and then divide that difference by the length of the specific response. So, you'd list each possible response for a question at the top or bottom of that column, and insert a column to contain the counts. Then in the empty cells next to the possible alternatives put a formula like this:

``````=(SUM(LEN(\$B\$6:\$B\$70))-SUM(LEN(SUBSTITUTE(\$B\$6:\$B\$70,B1,""))))/LEN(B1)
``````

This must be entered as an array formula, with `Ctrl` - `Shift` - `Enter`

This assumes your responses are in B6:B70. This is case-sensitive, so it wouldn't pick up your lower-case c's in your example criteria.

-

You can use text to columns with the custom delimiter `;#`. (Note: you may need to identify the delimiters separately and tell excel to `treat consecutive delimiters as one`) This will break the text out across a row.

Then you can `cut` and `paste special - transpose` The only downfall of this procedure is you will need to insert cells between the rows when you want to `paste special - transpose` because you can't `insert cells` that way. You could `transpose` them in a different way and then `cut - insert cells` to make it the way you want it.

-
Text to Columns only allows single-character delimiters. – Farray Feb 20 '12 at 15:50
@Farray Good point. You can identify both and tell excel to "treat consecutive delimiters as one". – Raystafarian Feb 20 '12 at 15:52