How Do I Remove Leading Zeroes and Change to Currency?

[txdave35]

Hey guys,

I have some raw data that I imported into a spreadsheet and I can't get the number format to convert to the correct currency amount. Here is an example:

The first amount appears like this and is currently set as a text field:

00142465

I need it to convert to $1,424.65. I tried change the data type to number then currency. I even tried manually removing the leading zeroes. Unfortunately, the result I always get is:

$142,465.00


I have 33,000 records so I can't manually remove the zeroes anyways. Can anyone help?

 

[SkipVought]

Hi,

Well you cell is/was formatted TEXT when it was imported, otherwise the leading zeros would not be there and you could format the cell as currency.

First you will need to CHANGE the value in the cell from TEXT to a NUMBER with the appropriate number of decimal places. This import probably came from a COBOL implied decimal format, I'd guess. Simply multiply the text value by .01. Then format

Remember, changing the FORMAT changes NOTHING in the underlying value!

 

[SkipVought]

So you're going to need to do this formula in an empty column an the COPY the column and PASTE SPECIAL--VALUES in this column.

Assuming a value in A2...

=A2*.01

in the empty column and copy/paste thru all rows of data.

 

[SkipVought]

Another possibility is to look at the method if getting the data into your workbook. If you know the data source and can query that source, you could do the math in the query, avoiding the formula/copy/paste special--values as a post process.