I have a column of mixed numerical and text data in each cell.

I would to apply a function, =SUM() say, to just the numerical data types of the columns. To sum up all the numerical data in the column.

I've tried doing =SUM(A1:A9) but the result is 0. I've also tried formatting the cells and have the Category as Number and Format as General. The result is still the same.