Solved: Excel - Text to rows

[Spratt_Cary]

I have a questionnaire that end users are completing. A few of the questions are multiple choice and they can choose as many of the responses that applies. The problem is, the application that pulls the data, puts all the responses in one cell divided with a colon. This makes it very difficult to calculate responses. Is there a way to extract these responses individually and insert a new cell(s) below the original in the same column without overwriting the data. There can be one response or 8 responses. See Attached example. column AO row 7, column AU, row 6. Thanks in advance for any help. (I have not been on VBA for awhile, feels good to be back)
Brenda

[mdmackillop]

Can you post a "desired result" sample?

[mdmackillop]

Try this.
Paste the code in the worksheet module. Doubleclick the multi-answer cell.
EDIT: You'll need to turn off the Autofilter to allow insertion of cells.
[vba]
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ans
Dim Cels As Long, i As Long
Cancel = True
ans = Split(Target, ":")
Cels = UBound(ans)
Target.Offset(1).Resize(Cels).Insert shift:=xlDown
For i = 0 To Cels
Target.Offset(i + 1) = ans(i)
Next
End Sub

[/vba]

[Spratt_Cary]

Not sure what I am doing wrong, but it does not seem to work for me. URGH!!!!!!!!!!

[Spratt_Cary]

Thanks mdmackillop, not sure what I am doing wrong. see attached
brenda

[mdmackillop]

Try this

[Spratt_Cary]

Man, you are very very good.

[cdc555]

hello

I need the exact same thing I think - I am very new to VBA - would it be possible to have a copy of your working example?
thank you, thank you in advance