Reputation: 467
I have an Excel spreadsheet that I have to export to a .txt file for import by another program. I have rows of currency fields that the other program expects to have lined up by their decimal place, like so:
$ 0.01
$ 0.10
$ 1.00
$ 10.00
$ 100.00
$ 1000.00
etc.
The "accounting" format in Excel will align the values correctly in the spreadsheet, but when I try saving it to file, It spits out something like:
$0.01
$0.10
$1.00
$10.00
$100.00
" $1,000.00 "
" $10,000.00 "
" $100,000.00 "
" $1,000,000.00 "
I tried playing with the custom formats, with similar luck. Is there a simple way to accomplish this?
Upvotes: 1
Views: 557
Reputation: 1715
You need to create a version of the column to export and populate it with a formula based on the TEXT() function. Something like:-
=TEXT(A1,"$?????????0.00")
where A1 contains the number to be formatted (and the number of ?, etc. provide the number of columns you require in your output).
reference is here : http://office.microsoft.com/en-gb/excel-help/text-function-HP010062580.aspx
Upvotes: 1