CSV import with Formatted numbers

C

Craig McDonald

Is there a way to format a CSV file such that numbers can
be formated (like # $,0.00) at the time they are
imported? We don't want to have people reformat the data
after the file is imported. We would like the numeric
picture to be included in the CSV data. This includes
formula fields as well.

A simple example of the CSV file would be:
UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
1002,John Doe,1000.00,1500.00,=Sum(C2:D2)
1005,Jane Roe,1420.00,2125.00,=Sum(C3:D3)

I want the Total (in column E) to be formatted such that
it looks like $2,500.00 and $3,545.00 when it is imported.

Thanks in advance.
 
S

Squid

Unless you could format the sales figures with a $ sign
when you create the CSV. Your sum formula will assume the
$ formatting.
UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
1002,John Doe,$1000.00,$1500.00,=Sum(C2:D2)
1005,Jane Roe,$1420.00,$2125.00,=Sum(C3:D3)
Click to expand...

will result in excel as:

UserId Name Oct_Sales Nov_Sales Tot_Sales
1002 John Doe $1,000.00 $1,500.00 $2,500.00
1005 Jane Roe $1,420.00 $2,125.00 $3,545.00
 
G

Guest

That does not work for me. The Sum column has no
formatting - commas or $. (Excel 2003)
 
C

Craig

This works, but is not right-aligned.
UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
1002,John Doe,$1000.00,$1500.00,=Text(Sum
(C2:D2), "$0,000.00")
1005,Jane Roe,$1420.00,$2125.00,=Text(Sum(C3:D3),
$0,000.00")

Is it better to export html with formatting and import it
into Excel?
 
Top