Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    retaining trailing zeros (XL 97 sr2 on Win 2000/NT

    I have a CSV file that contains some numbers that have trailing zeros e.g.
    24.1,15.0,13.1

    XL being helpful can read csv files without any problem so when I open the csv file I get an XL sheet holding each number in separate cell, trouble is XL (being less helpful) ditches the trailing zeros and I get :
    <table border=1><td>24.1</td><td>15</td><td>13.1</td></table>

    I have tried a less direct approach:
    - create a blank sheet, format all the cells to text
    - open the csv file in notepad then copy and paste the data into a single column
    - then use the text to columns feature making sure I select 'text' as the column data format
    but still the trailing zero disappears.

    I know mathematically the trailing zeros are not significant but I must at least capture them as text before I let XL loose to do its sums. Any ideas how I can get XL to import the csv file and treat everything it finds as text even if the variables between the commas are numbers?

    stuck

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 2000/NT

    Have you tried formatting the cells to "Number" and setting the decimal place to 1?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 2000/NT

    rename the file extension to txt and then use the open/import functionality to change the fields to text.

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    Sorry, should have explained that bit too. Using a fixed number of dps is not an option. The trailing zero(s) could be on the end of a value of any length.

    stuck

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    Should have made this clear as well. Doing this is the same as using the text to columns feature as I explained in my original post. While the final values in the sheet are text the trailng zero is lost.

    stuck

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 2000/NT

    An idea (Not completely worked out any details) might be to FORCE excel to not convert to a value. excel for ALL numbers uses 15 digits of precision.15 = 15.0 = 15.00 is actually 15.0000000000000. XL can NOT differentiate between any of those numbers. It can differentiate if the explicit FORMAT of the numbers ONLY.

    If you were to edit your CSV (in notepad or word) to add a "sticky-space" (ascii 160 = <alt>0160) either before or after the "number" excel will NOT convert it to a number and it will remain as TEXT. You can later use substitute to replace the sticky space with a null to get the value.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    It may not be fully worked out but it certainly is an idea...

    I am aware that XL works to 15 digts and when I let it loose to do the sums then I am quite happy for it to drop the trailing zeros but I must retain them as text first. So, tweaking the CSV file by adding a sticky space or a single opening quote to before a number does make XL treat it as text and as you say, it is easy subsequently to remove such markers. Perhaps the real fix is to get the format of the variables in the CSV file changed.

    Meanwhile, I have discovered tht if I open the csv file in word and convert the text to a table using the commas as the delimiter I can then paste special the table as text into an XL sheet formated as text and I get the desired effect. For now that will do, but I'd prefer a one stop shop XL solution.

    (thinking rather than) stuck

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    I now have a CSV file where the commas are followed by an apostrophe/single quote mark (') and everything is wonderful! Well no, actually I lied hence I'm back here again.

    What is the VBA equivalent of the SUBSTITUTE worksheet function? Do I just use Application.WorksheetFunction.Substitute or is there a more elegant trick?

    stuck

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    I usually use:
    Application.WorksheetFunction.Substitute

    since VB has no equivalent

    You could also use something like (str is the value to convert)
    val(mid(str,2,len(str)-1))
    to extract all but the apostrophe
    Steve

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: retaining trailing zeros (XL 97 sr2 on Win 200

    Hi stuck,

    If your csv data has a fixed set of formats (eg by column), it should be a fairly straight-forward matter to format the columns to show fixed decimals etc after importing.

    Conversely, if you have a spreadsheet with rows/columns/cells already formatted the way you want the imported data to appear, and you can rely on the import file's structure matching that layout, you could import the data using a macro like the attached (untested), which I modified from an old MS KB example. Note that running the macro could take a lot longer than simply opening a csv file.

    If your import data are not suffciently structured to use either of the above, you'd need to modify the code further to 'interpret' the formatting of each comma-separated value so that it could be applied on a cell-by-cell basis. You'll see a suggested insertion point about 1/2 way down.

    Cheers

    PS: You'll need to delete the line for creating a new workbook, which is what the macro does.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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