Hello,

I would love to know where in Excel Options (2010) I can tell the program to stop changing "1/3" into "01-Mar". I import (copy/paste) a lot of tables with fractions and they are never intended for dates.

Any help would be much appreciated. Thanks,

Bryan

Hi Bryan,

If you format the destination cells as 'fraction' or 'number' before pasting, that should resolve the issue. Both preserve them as values, though you'll tend to lose the fraction formatting with the latter.

AFAIK, the only other way to guarantee that pasted fractions are treated as such is to prefix them with a = or ' before copying & pasting. The former preserves them as values (though you'll tend to lose the fraction formatting) whilst the latter stores them as text.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for the reply, Paul!

Does that mean every time I open a few workbook I have to format the cells as numbers before I start?
Since that seems fairly redundant, I was wondering if I can change the options of the default "New Workbook" to have that done beforehand..

Bryan

Hi Bryan,

Unless fractions are what you work with pretty much all the time (ie not other number formats), prefixing them with the ' if you don't need to do calculations, or with = if you do need to do calculations is probably the safest course. With the latter, you can alway change the cell formatting if you need to, but the underlying data are preserved as you've input them. If you format the cells beforehand as 'fraction' and simply input the fractional value, Excel converts your input to a decimal value and merely displays it as a fraction - the actual value input is lost.

If you really wanted to, you could format an empty workbook in the 'fraction' format as Excel's default templates for both workbooks (Book.xlt) and worksheets (Sheet.xlt) and place them in Excel's start-up folder. That way, Excel will default to that format for new workbooks. The downside is that you'd then have to change the format for all the cells containing numbers that you want displayed as decimals - and Excel will still hold the underlying values for fractions in decimal form.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Put before ' , apostrapy before number like '1/3
you problem will solve

gsrikanth

FYI, this thread is 8 months old, and your suggestion was already made in post #2

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post