Converting a number to an Integer or a Double based on number after the decimal

Posted on 2008-11-14
Last Modified: 2012-05-05
I have a program that populates data onto an excel sheet.  I have the data in my SQL 2005 DB as a Numeric(8,2) datatype.

When I pull the data to populate the excel sheet, I want to either insert the number with a decimal or no decimal depending on the value.

So if the value is "4.00" I want to insert "4" to the excel sheet.  If the value is "4.25" I want to insert "4.25" to the excel sheet.

How do i do that?
Question by:logoncom
    LVL 12

    Expert Comment


    If you insert it as a number then Excel will always store it as decimal number behind the scenes.  You could store it as a string but I suspect that would not be the right option since Excel arithmentic would not work.

    Which leaves the column formatting in the excel sheet.  If you use Excel General formatting then you should get the results you want.  If you use Number that you will always get the number of decimal places specified on the format.

    Hope this helps
    If you want to inser

    Not much of a solution this
    LVL 23

    Accepted Solution

    Do you want to simply display this, or post an integer to the sheet?  Because, by default, Excel displays 4.0 as 4.
    Otherwise, if you are willing to send strings to Excel,
    Dim realVal as Double
    Dim outputString as String
    If realVal = System.Math.Floor(realVal) Then
        outputString = Cint(realVal).ToString
        outputString = realVal.ToString
    End If

    Author Closing Comment

    works, thanks

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now