Introduction

 

In the creation of an Excel workbook, sometimes a column contains numerical values stored as strings. For example, a percent column can display percents as strings. This poses a problem if you want to use these values for equations, or if you want to clean up the display of the file, as Excel will mark these strings with a green corner indicating that they can be displayed as numerals, along with a warning message, "The number in this cell is stored as text or preceded by an apostrophe."

ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.

Hence, converting the values for an entire column requires three steps:

1. Gather the values from the column into a collection and convert them to numeric values.
2. Apply the desired Number Format to the styles of column.
3. Cycle through the collection and add the values back to each cell of the column.

The following sample method takes a column of strings of the form "00%" and display them as percentages with no decimals.

Code