A1 is formatted as text and contains a decimal number I would like to programmatically convert to a number.
The "convert to number" function N() only works with whole numbers apparently.
Trying a trick like =A1+0 seems to similarly only work with whole numbers.
Use the function
value(A1)
to convert a string to a number.
Using VALUE() should work if you know that the cell will convert to a number
A nice way to populate worksheet range from common Data Structures (like DataTable) and still retaining the objects (number, dates, etc.) to get rid of the "convert to number error":
string[,] stringArray = new string[dataTable.Rows.Count, dataTable.Columns.Count];
for (int row = 0; row < dataTable.Rows.Count; ++row)
{
for (int col = 0; col < dataTable.Columns.Count; col++)
{
stringArray[row, col] = dataTable.Rows[row][col].ToString();
}
}
...
...
range.Value2 = stringArray;
range.Value = range.Value2;
From Excel 2013 there is a function called NUMBERVALUE (listed under Text). You can specify your own decimal and group seperation symbol independently from your computer's region settings. In my own languague it is named NUMERIEKE.WAARDE.
