Excel Number Formatting
Posted on 2014-12-19
Environment: Excel 2013 on Win 7 Pro x64, both fully patched
I have a spreadsheet column with about 5500 random numbers in it. I have a VB macro to compare these numbers to another column to detect duplicates. The column i am using for comparison was imported from a CSV file, and had spaces in it, which i used TRIM to fix. Now the entire column has the little green triangle indicating an error stating "The number is formatted as text or has a preceding apostrophe". I have selected the entire range of cells, and changed the format to Number on the home tab, but it only fixes the top most cell. I have also tried selecting the entire range, right clicking, and setting the cell format that way, but it has no effect. The green triangle remains. So far the only way I have gotten rid of it is to double click in each cell and then click elsewhere, which immediately correctly formats the cell, but I am not going to do that 5500 times. Please advise on how to correct the issue.