With the popularity of Excel, it is no wonder many third party applications offer a feature to allow you to export data directly into Excel. This is convenient and makes it easy to share information as almost everyone has Excel loaded on their computer. Unfortunately, often these files (due to poor coding from the third party application) do not have full functionality.
The Problem: Numeric Columns Don’t Add Up
Your boss receives a file from your purchasing department. It is a monthly breakdown parts expenditures for your maintenance department. The problem is, when he tries performing some basic arithmetic functions on the spreadsheet, the numbers don’t add up(literally). **Note the SUM() function in the picture below returns $0.00 even though there are positive values in the column above.
After attempting all the solutions he could think of, your boss sends the file to you hoping you can help him out.
The Solution: Hidden Characters
After receiving the file, you discover the cause of the problem is hidden characters. Whatever application this spreadsheet was exported with, added a few hidden gems to make sure the file is utterly useless to anyone trying do much more than just read the file.
So how do we fix the issue? First you will need a keyboard with a numeric keypad. Time to start digging through your desk drawers or making nice with the office hoarder. I am sure he has a few dozen keyboards squirreled away somewhere and can loan you one. Although it will probably be up to you to dust off the doughnut powder.
Okay, so you have your keyboard. Now what?
- Highlight cells you want to work with
- Go to Find&Select > Replace in the upper right corner of your ribbon bar
3. In the Find what: field – hold down the ALT key and type 0160 with the numeric keypad. ***note nothing will appear in the space as you type this
4. Leave Replace with: blank and hit the Replace All radio button
Now all of your numeric columns will function properly.You can send the file back to your boss, further cementing your reputation as the office Excel guru.