SUM specific numbers in 1 cell or extract information into a table to Calculate NPS

[Rooksboy1979]

[TABLE="width: 737"]
<TBODY>[TR]
[TD]Hi All

This is some data I extract from a report, the data appears all in one cell (A1):

0:0 (0.00%) 1:0 (0.00%) 2:0 (0.00%) 3:0 (0.00%) 4:0 (0.00%) 5:0 (0.00%) 6:1 (3.85%) 7:3 (11.54%) 8:11 (42.31%) 9:11 (42.31%)

What I would like to do is use a formula to calculate a net promoter score from between 0-9 using this data above without having to manually transfer it to individual cells as I have done below:

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: center"]B1 - B11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: center"]C1 - C11[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]2[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]3[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]4[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]6[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]7[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]8[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: center"]9[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]11[/TD]
[/TR]
</TBODY>[/TABLE]


Detractors: Anyone who scored 0 - 5 = 0
Passive: Anyone who scored 6 -7 = 4
Promoters: Anyone who scored 8 - 9 = 22
[TABLE="width: 355"]
<TBODY>[TR]
[TD="align: center"]Surveys Completed</SPAN>[/TD]
[TD="align: center"] Detractors</SPAN>[/TD]
[TD="align: center"] Passive</SPAN>[/TD]
[TD="align: center"] Promoters</SPAN>[/TD]
[TD="align: center"]NPS</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]26</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]4</SPAN>[/TD]
[TD="align: center"]22</SPAN>[/TD]
[TD="align: center"]85 </SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

I sum these up each total, then use the formula below to get the above NPS score.

=IF(H14="","",SUM(K14-I14)/H14*100)
</SPAN>
If there is a way to do this without having to take the data manually out of cell A1 that would be great, unfortunaly my knowledge of excel is pretty basic when it comes to formula's.

I am using Excel 2007, however I also use Excel 2003 from time to time. I hope I have explained myself clearly, any questions please ask.[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>Thanks for looking, any help I can get would be greatly appreciated.
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]