Announcement

Collapse
No announcement yet.

Format Numbers Greater Than 15 Digits

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Format Numbers Greater Than 15 Digits

    I am inserting data into my spreadsheet using VBA code to read a file and insert the data into the relevant cells. My problem arises when I have a string such as 80830410205724044. The actual code that inserts the data is as shown (temp is dimmed as string)
    Code:
          Call select_cell(col)
          Select Case True
            Case Mid(temp, 5, 1) = "-" And Mid(temp, 8, 1) = "-" And _
                      Mid(temp, 11, 1) = " " And Mid(temp, 14, 1) = ":"
              ' Timestamp (we assume) - format it thereafter. Do NOT test for a length of 26
              ' this will not always be true
              Selection.NumberFormat = "@"
            Case Mid(temp, 5, 1) = "-" And Mid(temp, 8, 1) = "-" And _
                    Len(temp) = 10
              ' Date (we assume) - format it thereafter
              Selection.NumberFormat = "yyyy/mm/dd;@"
            Case IsNumeric(temp) And Len(temp) > 15
              ' Content is LARGE numeric - format it as character
              temp = "'" & temp
            Case IsNumeric(temp)
              ' Content is numeric - add 0 to avoid strange formatting issues
              temp = temp + 0
              Selection.NumberFormat = "0" ' What happens with decimals ????
          End Select
          
          Cells(row_A_pointer, col) = temp
    As can be seen in the code above, I have resorted to appending a quote in front of the "number" - this works fine apart for the fact that all these cells then have a an info icon in front of them with "the number in this cell is formatted as text or preceded by an apostrophe". Is there any way of inserting the number WITHOUT resorting to this solution ?

  • #2
    Re: Format Numbers Greater Than 15 Characters

    Not sure if I should append here as an aside to the append above ....

    Am running Firefox, and if I want to append a new topic, I get suggestions for possible solutions. All well and good. Trouble is (for me), if I select the first suggestion and discover it's not relevant, I see no back button that will allow me to review the second suggestion etc etc.

    Am I missing something here ?

    Comment


    • #3
      Re: Format Numbers Greater Than 15 Characters

      Get rid of the quote character and just add the number of decimal places you need to the format.

      Code:
      Selection.NumberFormat = "0.00000000000000"
      AAE
      ----------------------------------------------------

      Forum Rules | Message to Cross Posters | How to use Tags

      Comment


      • #4
        Re: Format Numbers Greater Than 15 Characters

        Thanks AAE, but there are NO decimals. It's just a 17 digit "number"

        Comment


        • #5
          Re: Format Numbers Greater Than 15 Characters

          Oops!. Mis-read the question and made a wrong association based on your remark "what happens with decimals?"

          My problem arises when I have a string such as 80830410205724044.
          You need to accurately define what the problem is. You remark in your code about "strange formatting issues." This doesn't tell us much - too generic. I assume you are importing from another Excel file. If so, perhaps the strange formatting issue lies with the formatting of the source data. Since you didn't post your full code, I will only suggest you consider pasting in the data as values only and then apply the desired format via your Select Case routine.

          Relative to your thread title:
          I see no reason why you cannot remove the quote and import the data as a number. You are already testing it as valid number using "IsNumeric".

          Why are you formatting a numeric string as text when the length is greater than 15?
          Code:
          Case IsNumeric(temp) And Len(temp) > 15 
               ' Content is LARGE numeric - format it as character
              temp = "'" & temp
          Here you make no distinction about length of the string.
          Code:
          Case IsNumeric(temp) 
               ' Content is numeric - add 0 to avoid strange formatting issues
              temp = temp + 0 
              Selection.NumberFormat = "0" ' What happens with decimals ????
          There is no reason to convert a large numeric string to text and it is bad practice to do so. I would delete that part of the code. Numbers hshould ALWAYS be formatted as numbers.

          As for "what happens with decimals?" - Your format specifies no decimals. Thus Excel will likely round the number. If you need decimals, change the format, using my first example adjusted for the number of places you require.
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment


          • #6
            Re: Format Numbers Greater Than 15 Characters

            Thanks for taking the time to answer this. Trouble is with trying to explain a problem is that you're so immersed in it, you forget that others aren't. Okay. Here's an attempt at explaining what's going on(hopefully without being too verbose).

            First of all - input to the program is via a simple text file that contains unloaded columns/tables from DB2. For example,
            *** Unloading A08A0200 *** (17 columns)
            *** Selection SELECT * from KOBA.A08A0200 WHERE ARIDFR = '80830410205724044 ' order by PDARIDFR,EFFDT
            PDARIDFR 1200234855
            ISOCOUNTRYCODE SE
            EFFDT 2027-12-22
            ENDDT 9999-12-31
            ARIDFR 80830410205724044
            SORTKOD 7
            ISOCURRCODE SEK
            PDARIDFRX
            EXEKUTIV_NIVA_J_N N
            PDARLCSTATUS 7
            IBANNR
            PDIDFR 1
            SWIFTADRESS
            PDARPDARX 1200234715
            LVLCODE 0002
            TACKNINGSKONTROLL 2
            SWIFTADRESSPDARX
            (In reality, the column names and their contents are aligned neatly under each other).
            The data is organised so that each "row" of DB2 data consists (in the example above) of 17 rows, one for each column. I want to "import" it into Excel so that it is transposed and one row of DB2 data becomes one row in Excel with 17 columns. (The idea being then to color-code the cells/rows to indicate whether data has been changed or a row been inserted/deleted).

            For the code I included in the first append, I have the content of the DB2 column (1200234855 for PDARIDFR in the first example, SE for ISOCOUNTRYCODE in the second etc etc) in the variable temp (dimmed as string). My problem arises when I want to set the content of a cell to the value 80830410205724044. If I change the code so it becomes
            Code:
                    Case IsNumeric(temp)
                      ' Content is numeric
                      n1 = InStr(1, temp, ".")
                      If n1 = 0 Then
                        Selection.NumberFormat = "0" ' No decimals
                        n2 = 0
                      Else
                        n2 = Len(temp) - n1
                        Selection.NumberFormat = "0." & Mid("000000000000000", 1, n2) ' Max 15 Decimals
                      End If
            (please bear with my variations - I'm attempting to pinpoint the correct value), I get the value 80830410205724000 (last 2 digits lost). I then changed the code above so it became
            Code:
                    Case IsNumeric(temp)
                      ' Content is numeric
                      n1 = InStr(1, temp, ".")
                      If n1 = 0 Then
                        Selection.NumberFormat = "0" ' No decimals
                        n2 = 0
                      Else
                        n2 = Len(temp) - n1
                        Selection.NumberFormat = "0." & Mid("000000000000000", 1, n2) ' Max 15 Decimals
                      End If
                      temp = temp + 0
            and I got ######## in some cells (when I selected the cell, it showed the value 8,0830410205724E+29), in other cells with the same value I see 808304102057240000000000000000
            (albeit 8,0830410205724E+29 when I click on the cell).

            So far, the only work-around I can see is to append an apostrophe in front of 80830410205724044, but then of course, I get the info icon next to the cell with the message about the apostrophe.

            Does that explain matters a bit better ????

            So, it boils down to, how I "tell" Excel that the string 80830410205724044 is a character string and NOT a number ?

            Comment


            • #7
              Re: Format Numbers Greater Than 15 Characters

              I got ######## in some cells
              Cell width is too narrow to display all of the characters. Adjust cell width.

              (when I selected the cell, it showed the value 8,0830410205724E+29)
              This is scientific notation, which Excel defaults to for large number values or when the number of places to the right of the decimal exceed the display precision.

              Should have caught this earlier, but Excel is limited to a display precision of 15 significant numbers, but will internally store the precise number value whatever its number of places.

              You can force Excel to display exactly the value you enter by changing the precision of calculations to use the displayed value. I do not recommend this as it affect calculations in all workbooks.

              So, it boils down to, how I "tell" Excel that the string 80830410205724044 is a character string and NOT a number ?
              This confuses me because you have implied at the start you wanted a number and asked about decimals.

              If you are not using the number in a formula, then you will have to format it as text, as you have done, in order to display all of the numbers. If the number is used in a formula, you could simply copy it to another column (hidden if desired) and let Excel format it as scientific notation and reference that range as needed.

              For text, drop test for length and just use
              Case IsNumeric(temp)
              ' Content is LARGE numeric - format it as character
              temp = "'" & temp
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment


              • #8
                Re: Format Numbers Greater Than 15 Characters

                Excel only is accurate up to 15 digits.
                = (12345678901234567 = 12345678901234568) is evaluated as TRUE

                Comment


                • #9
                  Re: Format Numbers Greater Than 15 Characters

                  Thanks to both of you. AAE - your suggestion is what I originally had. The idea behind the test for the length is that if I prefix the quote for all numbers, I then get the green triangle at the top-left of the cell telling me that the text contains an apostrophe (or whatever I wrote earlier). By checking for the length, I ensure that ONLY those numbers that are larger than 15 digits will be formatted as text (albeit with a preceding quote) whilst other, "normal" numbers are formatted correctly.

                  I forgot to mention that the contents of the cells are really only for review purposes - they're not going to be used in any calculations or what not.

                  Comment


                  • #10
                    Re: Format Numbers Greater Than 15 Characters

                    If the green triangles are distracting, you can turn them off. Tools>Options Error Checking tab. (In Excel 2003)
                    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                    Comment


                    • #11
                      Re: Format Numbers Greater Than 15 Digits

                      As has been said, Excels limit for numbers is 15 digits, after the 15th digit it looses accuracy. So you have 3 choices;

                      1) Store the number as text
                      2) Live with the inaccuracy
                      3) Use another Application that IS suited to huge numbers.

                      Comment


                      • #12
                        Re: Format Numbers Greater Than 15 Digits

                        Thanks everyone for the suggestions. The solution that seems to solve the problem was
                        Code:
                              Select Case True
                                Case Mid(temp, 5, 1) = "-" And Mid(temp, 8, 1) = "-" And _
                                          Mid(temp, 11, 1) = " " And Mid(temp, 14, 1) = ":"
                                  ' Timestamp (we assume) - format it thereafter. Do NOT test for a length of 26
                                  ' this will not always be true
                                  Selection.NumberFormat = "@"
                                Case Mid(temp, 5, 1) = "-" And Mid(temp, 8, 1) = "-" And _
                                        Len(temp) = 10
                                  ' Date (we assume) - format it thereafter
                                  Selection.NumberFormat = "yyyy/mm/dd;@"
                                Case IsNumeric(temp) And Len(temp) > 15
                                  ' Content is LARGE numeric - format it as character
                                 Selection.NumberFormat = "@"
                                  ActiveCell.FormulaR1C1 = temp
                                Case IsNumeric(temp)
                                  ' Content is numeric - add 0 to avoid strange formatting issues
                                  n1 = InStr(1, temp, ".")
                                  If n1 = 0 Then
                                    Selection.NumberFormat = "0" ' No decimals
                                    n2 = 0
                                  Else
                                    n2 = Len(temp) - n1
                                    Selection.NumberFormat = "0." & Mid("000000000000000", 1, n2) ' Max 15 Decimals
                                  End If
                                           
                              End Select
                              
                              Cells(row_A_pointer, col) = temp

                        Comment

                        Working...
                        X