How to check if cell value is a number

Question

Cells of Excel spreadsheet are read using Microsoft.Office.Interop.Excel object. I need to find out formatting for number values and apply accordingly. For instance I have 19.0000 value but when it is read Value2 will be "19" however I need to keep it "19.0000"

Excel.Range sheetRange = xlWorksheet.Range["A1", lastCell];
var cell = sheetRange.Cells[row, col];
cell.Value2 == "19";

There is a NumberFormat property that returns formatting string like "0.0000" that I could use, but I can't find out how to check if cell value is a number.

I might not be understanding your question, but what about just using the test first: `IsNumeric(cell.Value2)` — John Bustos, Mar 07 '13 at 17:44
Sorry, it's a VB construct - check this out: http://stackoverflow.com/questions/437882/what-is-the-c-sharp-equivalent-of-nan-or-isnumeric — John Bustos, Mar 07 '13 at 18:21

score 0 · Answer 1 · answered Jul 29 '16 at 14:31

0

a bit of a hacky way around it is to add an apostrophe at the beginning - excel wont try to format it then

for instance var value = 19.0000; cell.value = "'" + value;

answered Jul 29 '16 at 14:31

Phyushin

1
2

score 0 · Answer 2 · answered Jul 29 '16 at 14:42

I use this function for that purpose:

    public static bool IsValidDecimalNumber(this string s)
    {
        if (string.IsNullOrWhiteSpace(s)) return false; //blank/null strings aren't valid decimal numbers
        return !s.Any(c => !(char.IsDigit(c) || c == '.')) && !(s.Count(c => c == '.') > 1);
    }

Edit: to elaborate, it returns false if it's blank/null, then it returns false if any digits aren't a number or a decimal point, and it returns false if there's more than one decimal point. Otherwise, it returns true.

How to check if cell value is a number

2 Answers2