Export Bad Amounts (2002)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

When I look at the text file, the first two amounts are in quotes and the next one is not.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote