Converting data in General Format cell to a number

[PE]

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

 

[Niek Otten]

Hi Al,

Format an empty cell as Number. Enter the number 1 in it. Edit>Copy.
Select your numbers. Edit>Paste Special, check Multiply.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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
|
|

 

[Guest]

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:

 

[Lori]

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.