Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Format Numbers Greater Than 15 Digits

  1. #1
    Join Date
    29th December 2006
    Location
    Stockholm, Sweden
    Posts
    78

    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)
    VB:
    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 ?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    29th December 2006
    Location
    Stockholm, Sweden
    Posts
    78

    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 ?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,096

    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.

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

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

  4. #4
    Join Date
    29th December 2006
    Location
    Stockholm, Sweden
    Posts
    78

    Re: Format Numbers Greater Than 15 Characters

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,096

    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?
    VB:
    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.
    VB:
    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

  6. #6
    Join Date
    29th December 2006
    Location
    Stockholm, Sweden
    Posts
    78

    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
    VB:
    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
    VB:
    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 
        [B]temp = temp + 0[/B] 
    
    
    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 ?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,096

    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

  8. #8
    Join Date
    23rd April 2007
    Posts
    3,339

    Re: Format Numbers Greater Than 15 Characters

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    29th December 2006
    Location
    Stockholm, Sweden
    Posts
    78

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,229

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Find Replace Numbers Greater Than & Less Than 2 Numbers
    By Psycho-linguist in forum EXCEL HELP
    Replies: 2
    Last Post: June 11th, 2008, 03:49
  2. Reduce Numbers To X Digits
    By rico355 in forum EXCEL HELP
    Replies: 3
    Last Post: May 8th, 2008, 05:44
  3. Delete First x Digits In Numbers
    By TaiChi56 in forum EXCEL HELP
    Replies: 9
    Last Post: October 14th, 2007, 14:43
  4. 16 digits numbers
    By ismail in forum EXCEL HELP
    Replies: 3
    Last Post: August 31st, 2005, 19:39
  5. Replies: 11
    Last Post: October 30th, 2004, 00:31

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno