Reputation: 948

How to Convert a Float String to Number?

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.

Upvotes: 6

Answers (4)

Elmex80s

Reputation: 3504

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.

Upvotes: 1

deb

Reputation: 21

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;

Upvotes: 2