Impossible to sum numbers in Excel

philip369 

If your numbers are formatted as Text, changing the formula of existing numbers as General or Number won't change them back to real numbers.

To easily convert numbers entered as Text back to the real numbers, select the numbers and follow these steps...

1) Go to Data Tab.

2) Click on Text to Columns and click on Finish in the next window which pops up which is Text to Columns wizard window.

As a result, your numbers as Text will be converted into the real numbers and your existing Sum formula will return the correct output.

philip369 

Most likely, your "numbers" are actually TEXT. Make sure that they are numbers.

philip369  I had the same problem when cutting and pasting from a web page. Numbers were text. All I did was a Replace of space to @ then replace the @ with nothing. Trying to replace space with nothing did exactly that - nothing - it didn't remove the spaces. 

Text to columns did nothing, I have yet to come across anything where that function works to be honest and I've been using Excel since it came out (I'm an old programmer!).

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.

philip369 

A different strategy would be to accept the values as text, potentially with embedded hard spaces, currency symbols, comma group separators etc, and use a Lambda function to strip out unwanted characters and put the pieces together once more as numbers.

"Worksheet formula"
= SUMTEXTλ(out) 

"where the function is:"
SUMTEXTλ = LAMBDA(text, 
    SUM(
        IFERROR(
            BYROW(text, LAMBDA(x,
                VALUE(CONCAT(REGEXEXTRACT(x, "(\d|\-|\.)+",1)))
            )),
        0)
    ));

IFERROR is intended to pick up the situation where there are no numbers in a text string.