Number formatting issues

[fgwiii]

An ongoing issue I have had is with one of the columns in the reports I
am accessing. One of the fields I access has a numbers in it such as
"063740285007037" but when they are copied and pasted into another sheet
"6.37403E+13" is displayed. Is there any way to correct this after the data
has been pasted?

Thank you for your help.

 

[Dave Peterson]

Change the number format of that range.

Select the range to fix
Format|cells|Number tab|custom category
type:
000000000000000

(as many as you need to keep the leading 0's)

Or maybe you could paste a second time:
Edit|Paste special|paste formats

(you'll get all the formatting--not just the numberformat, though.)

 

[Ron Rosenfeld]

An ongoing issue I have had is with one of the columns in the reports I
am accessing. One of the fields I access has a numbers in it such as
"063740285007037" but when they are copied and pasted into another sheet
"6.37403E+13" is displayed. Is there any way to correct this after the data
has been pasted?

Thank you for your help.

So long as the number has no more than 15 digits, you can use a custom format
(Format/Cells/Number/Custom Type 000000000000000 if you want to retain the
leading zero's, or merely Number with 0 decimal places if that is not
necessary.

OR, you can format the cell as TEXT prior to entering/pasting the value.
OR, you can precede the value with a single quote (which will not show up in
the display, but tells Excel to treat the subsequent characters as text).
--ron

 

[fgwiii]

That did the trick!

Is there a way to apply this format to the same column (E) on every sheet in
a workbook?

Thank you,

Fred

 

[Dave Peterson]

You can rightclick on a worksheet tab and choose "Select All Sheets".

Then almost any change you make to any of those selected sheets will be made to
all of them.

When you're done, remember to rightclick on any of those selected sheets and
choose "Ungroup Sheets". It can be a dangerous feature.

If you only wanted to do this to a few (not all) sheets, you can select the
first and ctrl-click on subsequent tabs. But remember to ungroup them when
you're done.