Results 1 to 8 of 8
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Export Bad Amounts (2002)

    The attached file has a list of amounts. They are formated currency, 2 decimal, and dollar symbol. When I export it to a text file the first two amounts are text the third is currency. About a third of the rows are text and the rest currency. It should all be currency. I just don't see what's going wrong with the xls file or data in it. Can any one help?
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Export Bad Amounts (2002)

    How do you determine what is text and what is currency in a text file?

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Export Bad Amounts (2002)

    When I look at the text file, the first two amounts are in quotes and the next one is not.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Export Bad Amounts (2002)

    How exactly are you exporting the spreadsheet to text? None of the different options I see include quotes. But try the following:
    - Select the used range.
    - Select Format | Cells...
    - Clear the Thousands Separator check box.
    - Click OK.
    - Export to a text file.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Export Bad Amounts (2002)

    I am using File | Save As and using the Text (tab delimited). I tried formatting the column as numbers instead of currency but I still get the same problem. Looks so strange to me. I even tried retyping the amount and I still get the same problem. It's behaving like certain cells are text but the format cell dialog tells me it is currency or number in my other test. Really strange.
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Export Bad Amounts (2002)

    Sorry, I can't reproduce the problem on my PC, so I have no more suggestions. I hope that others will be able to help.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Bad Amounts (2002)

    I was able to reproduce your problem on my PC. It appears that the issue is in the format (Currency, 2 decimal places using a comma) of the cells. When you save it as a Text (tab delimited) those cells whose value is greater than $999.99 appears with the quotes ie "$1,000.99" not $1,000.00

    If possible try using Han's suggestion of changing the cell format to "Number and remove the tick mark from Using 1000 Separator".

    I tried Han's suggestion and it appears that all lines are treated the same.

    John

  8. #8
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Export Bad Amounts (2002)

    Following Hans' instructions to the letter I got it to work just like you said. The first time I didn't remove the comma but I didn't think it was critical. Was I wrong! A simple comma!

    I also didn't notice the pattern that all the numbers in quotes were greater than 999.99 like you did.

    I'm always learning from you all. Thank you for the help.

    Lesson learned, follow the details of every instructions. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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