locked
Avoid "number stored as text" errors RRS feed

  • Question

  • I've written some code in VB.NET that exports data to Excel. The data is mostly numbers but text can occur as well. So I send it as String values to Excel. I get the error "Number stored as text".I checked the format of the cells that show the error and it is General and not Text.

    Could some one tell me how to avoid this error or prevent Excel from showing this error.

    Friday, February 11, 2011 6:13 AM

Answers

  • I think that if you define your variables as Object instead of String, then if Excel understands the value to be numeric (or a date or time), it will store the numeric value, otherwise it will be stored as text.

    It is also possible to prevent Excel checking for numbers stored as text. In Excel 2010, you can fine this at File | Options | Formulas.  Keep in mind that changing this option affects the way this installation of Excel treats all workbooks, it is not a property of the workbook itself.

    • Marked as answer by Purpoise Monday, February 14, 2011 5:52 AM
    Saturday, February 12, 2011 5:40 PM

All replies

  • I think that if you define your variables as Object instead of String, then if Excel understands the value to be numeric (or a date or time), it will store the numeric value, otherwise it will be stored as text.

    It is also possible to prevent Excel checking for numbers stored as text. In Excel 2010, you can fine this at File | Options | Formulas.  Keep in mind that changing this option affects the way this installation of Excel treats all workbooks, it is not a property of the workbook itself.

    • Marked as answer by Purpoise Monday, February 14, 2011 5:52 AM
    Saturday, February 12, 2011 5:40 PM
  • If the entire column is just numbers then you should be formatting the column as numeric before inserting the data into its cells  to make the warning go away.  This format can be set programmatically as well.  The default for a column is "general" type and that will cause Excel to warn you in a friendly yet exceedingly annoying way that it thinks it knows better than you about your data.  Sorry, do I sound jaded by this completely inane feature...  I mean, this horrible atrocity of a...  oh, forget it!  I can't find a nice way to talk about it. ;)
    Saturday, February 12, 2011 11:27 PM
  • Thanks Blackwood and Dig-Boy. I'll give things a try @work tomorrow.
    Sunday, February 13, 2011 1:06 PM
  • Hi,

    I faced the exact same problem last week. I solved the issue by exporting the data in the correct format to Excel. Number as number and text as text by trying to convert the string into a number before exporting. If convesion is successfull, I export the number. If not, I export as text. No formatting or option's change in Excel is needed.

     

        Dim objdata As Object 'Populated with data you want to export
        Dim decData As Decimal 'Holds data as number if conversion was successfull
    
        If Decimal.TryParse(objdata.ToString, decData) Then
          'Export as number
          Worksheet.Range("A1").value = decData
        Else
          'Export as text
          Worksheet.Range("A1").value = objdata.ToString    <br/>
        End If
    

    Code typed in notepad. So sorry if I have a typo somewhere.

    Hope it helps!

     

    Sunday, February 13, 2011 3:50 PM
  • I defined the variables as Object as suggested by Blackwood and it works for me. Didn't have time to try Feraud's suggestion. Thanks everyone.
    Monday, February 14, 2011 5:54 AM