Impossible to sum numbers in Excel

philip369 

I realise that people keep arguing about this issue so here's some test data that we received from a Powerpoint table.

Here's the ultimate solution.

1) Your numbers are likely saved as text and excel needs to be reminded to process them as numbers.

* If it is a single cell and there's a green triangle in the upper corner, try clicking on it and convert it manually.

* If it is a column of numbers, your first instinct should be to use the "Text to Columns" tool to change the data type into standard. Select the column > Data Tab > Text to Columns > Finish.

* If it's a whole table, use the "Text to Columns" tool, column by column.

 Select the column > Data Tab > Text to Columns > Finish.

2) If the above doesn't work, your numbers probably have a hidden character somewhere which forces excel to consider them as text. To know if there are any hidden characters in cell A1

* Click on B1 > Type formula =LEN(A1) and if there is a difference between the characters you see and the number of characters written in B1 then you'll know that there's some shenanigans somewhere. The likely culprit is CHAR(160) which is both invisible and unselectable but it's not always the case. 

You can use =TRIM(CLEAN(A1)) to try and find the hidden characters or use VBA/Powerquery but not all of them are destroyed and some hidden characters might escape detection

If all else fails, copy the offending data into another excel sheet and save as Tab Delimited Text (. txt)

It will save all data into UTF-8 and show the hidden characters that are causing the issue. Be warned as it replace all formulas into their corresponding result. 

Using the above example, you should have this as a result:

NB?
73?

You can then proceed to replace the "?" by "" using CTRL + H, then paste back the values into your excel sheet.

3) Someone else has mentioned using a Python Script to clean the table but not everyone has access to it and I am pretty sure that it will depend on a case-to-case basis. 

In any case, I hope it helps.
If someone has a better solution, I would be very interested to hear about it. I have enclosed the offending columns in my answer.