Convert Text to Number in Excel 2010

In my case, i want to try to summarize total record which the data taken from my Outlook Email. So i copy the data to Excel 2010, than i use text to column function to separate the columns. So in my excel look like picture below:


Then i want to summarize data in column B, but the result is not like I expected. The summarize just only took data from 10th row. Please see picture below:


This is happen because all data in column B is in Text format, and contain &nbsp(from HTML) character in begining and end of cells , except for row number ten. So it is can not be convert to number easily.

To solve this problem, i convert Text to Number forcely with tricky Paste Special:
1. Remove space (&nbsp) character in begining and end of cells with this function
    =(TRIM(SUBSTITUTE(B1,CHAR(160),CHAR(32))))+0

2.  Write 0 in other cell, then Copy the cell
2. Select the range cells in column C
3. Right click the selected range cells, and choose Paste Special option from context menu


4. In Paste Special dialog box, choose chek the All option and Add option, then click OK button


5. You can see total in column C is now the right value