Forum Discussion
Impossible to sum numbers in Excel
Funny this came up again today as I am working on another big project in Excel and where this ridiculous problem seems to happen over and over and over... Why can't Microsoft fix one of the most basic things ever? Like having a spreadsheet that can sum numbers? A crazy thought, I know 🙂
Riny_van_EekelenI'm going to have to go back and review these posts again because the problem persists even with new documents I create.
But I don't understand the meaning of this sentence-->
where users think that formatting a cell as a number doesn't change the content to a number if it is a text
Can you explain what that means?
Even when I have changed it to "number" it still doesn't work. Isn't "number" the default?
philip369 When you enter a number in the grid with the keyboard, Excel will always now it's a number. Even when you accidentally hit the space bar a few times before the numbers. Provided that the cell where you enter a number is not formatted as Text.
However, when you copy a number from, let's say a website or open a CSV file, it may happen that the numbers come across as texts. Once that has happened you can NOT just format the cell to Number to transform it into a real number. After you have formatted the cell as Number you need to re-enter the content of the cell (F2 and Enter). Only then the 'text-number' becomes a real number.
You can use tools like Text-to-columns to fix such issues once they have occurred. But it's better to prevent them from happening. For instance, when you have a CSV file, DO NOT open it directly in Excel. Instead, import it via Power Query or the Legacy 'From Text' import wizard.
It happens quite often that people want to import product numbers that have leading zeroes. And then they get upset because Excel guessed that these should be numbers and deleted the leading zeroes. So that's the opposite from your situation.
The point is that the user needs to know his/her data and not let Excel guess how to interpret numbers. Same thing happens with dates, over and over again. The user must be explicit.
Riny_van_EekelenI had a similar issue with some financial data imported from a bank site. After noodling around, I realized that the bank website was using an em-dash for a minus sign! A quick S&R replacing the copied dash with a normal minus-sign and everything worked!
(I'll be in trouch with this bank to ask why the hell they're using em-dashes for minus-signs, too!)
