Currency formatting dilemna

[Eddie G.]

I import data into a CSV file then convert it to excel xls. One column of data has a comma in the number like this 19,456 which actually that is supposed to be 194.56 (yes the mainframe is screwed up and sends the data with the comma in the wrong position.) Anyhow, I have to convert this to a currency figure so using the following:
=left(b2,3)&"."&right(b2,2)
I get 194.56 then I copy and paste values over it to get rid of the formula, but then i cannot get the $ formatting to work. How do I get the currency or accounting or dollar formatting to work on the resulting cell?

Eddie G.

[Tom Schreiner]

Hi Eddie.
Give this a try...
=TEXT(LEFT(B2,3)&"."&RIGHT(B2,2),"$#,##0.00_);($#,##0.00)")

Tom

[Mark W.]

On 2002-04-16 07:27, Eddie G. wrote:
I import data into a CSV file then convert it to excel xls. One column of data has a comma in the number like this 19,456 which actually that is supposed to be 194.56 (yes the mainframe is screwed up and sends the data with the comma in the wrong position.) Anyhow, I have to convert this to a currency figure so using the following:
=left(b2,3)&"."&right(b2,2)
I get 194.56 then I copy and paste values over it to get rid of the formula, but then i cannot get the $ formatting to work. How do I get the currency or accounting or dollar formatting to work on the resulting cell?

Eddie G.

After you paste your "new" values, select 'em, choose the Data | Text to Columns... menu command, and press [ Finish ].

[Eddie G.]

thanks to both responses....both ways work great.

[Dave Patton]

Make the conversion a VALUE by


a) copy a blank cell
select cell with converted text
Edit Paste Special Add

b) use Value
=VALUE(LEFT(B2,3)&"."&RIGHT(B2,2))

With either of the above, format as Currency