Ed Beaty
Ed Beaty

Reputation: 467

Format currency in Excel so decimals align on export?

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

Answers (1)

db9dreamer
db9dreamer

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

Related Questions