I have received a spreadsheet with hundreds of cells containing "numbers" in
the form 00000000002178 - this is in fact $21.78 but all the cells are set
to General format and the data is not treated as numbers.

Is there an easy way to convert this data into their currency values? I
have tried to set the cells to numerical format with 2 decimal places but
this has no effect on the data. I have to go to each cell, convert the
format and then key in the number - this will take forever.

Hope someone can help

Al

Hi Al,

Format an empty cell as Number. Enter the number 1 in it. EditCopy.
Select your numbers. EditPaste Special, check Multiply.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"PE" wrote in message ...
|I have received a spreadsheet with hundreds of cells containing "numbers" in
| the form 00000000002178 - this is in fact $21.78 but all the cells are set
| to General format and the data is not treated as numbers.
|
| Is there an easy way to convert this data into their currency values? I
| have tried to set the cells to numerical format with 2 decimal places but
| this has no effect on the data. I have to go to each cell, convert the
| format and then key in the number - this will take forever.
|
| Hope someone can help
|
| Al
|
|

Use a helper column with formula
=VALUE(A2/100)
You can then copy and pastespecial/Values into the original column if
necessary.

Regards,
Stefi

€žPE€� ezt Ã*rta:

I have received a spreadsheet with hundreds of cells containing "numbers" in
the form 00000000002178 - this is in fact $21.78 but all the cells are set
to General format and the data is not treated as numbers.

Is there an easy way to convert this data into their currency values? I
have tried to set the cells to numerical format with 2 decimal places but
this has no effect on the data. I have to go to each cell, convert the
format and then key in the number - this will take forever.

Hope someone can help

Al

If cells are formatted as text, choose Edit-Replace 0 with 0 then
divide by 100.
Or type 100 in a cell, copy and paste special over the range with
options values and divide.

PE wrote:

I have received a spreadsheet with hundreds of cells containing "numbers" in
the form 00000000002178 - this is in fact $21.78 but all the cells are set
to General format and the data is not treated as numbers.

Is there an easy way to convert this data into their currency values? I
have tried to set the cells to numerical format with 2 decimal places but
this has no effect on the data. I have to go to each cell, convert the
format and then key in the number - this will take forever.

Hope someone can help

Al