Cell formatting behaviour question

[derek]

Hey guys,
I have 2 related cases that I dont understand how to get Excel formatting to
work:

1. If I imported or copied rows of data (numerical) from another file (Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands, etc.)
and it will not show up. However, if i then double-click inside a cell (as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need to
force excel to display the formatting i want immediately. I.e. if i select
number format, with 2 decimal and comma separation, i want excel to display
that way.

2. Very similar case... sometimes if I import from Access, numbers that are
long, excel will display as 1E09 etc. I want to display the full number, but
it only works when i double-click to edit contents of the cell and then hit
enter.

I have tried manipulations, including copy values only to another column and
then apply the formatting fresh, etc. Doesn't work.

Very frustrating!!

Any help appreciated, thanks

 

[Gord Dibben]

Derek

Try this.

Format the cells to Number or General then copy an empty cell.

Paste Special>Vlaues>Add>OK>Esc.

Your numbers should now be real numbers and can be formatted as you like.

Has to be done after the import/copy unless you use some sort of event code
when importing/copying to the sheet.


Gord Dibben Excel MVP

 

[derek]

Thanks Gord

A friend also just found a similar way, he went to a new column and did "=A1
+ 0" for example. The new cells were properly formatted, then he pasted the
values back where i wanted them

Seems pretty silly, but it works, thanks!

 

[Gord Dibben]

And a lot more more work that my method<g>


Gord