decimal format preferences??

  • Thread starter Thread starter bubba1965
  • Start date Start date
B

bubba1965

Is there any way to set up in Excel the default format when pasting tex
into Excel from other applications in the following manner. So that al
numbers will have three numbers after the decimal point and that if th
number is less than one that it NOT have the zero before the decima
point.

In other words, I want numbers like 0.125 to come in as .125 and no
0.125. Also I would like numbers like 0.120 to come in as .120 and no
.12 or 0.12.

So don't cut off the zero if it appears as the third number after th
decimal point and don't add the zero before the decimal point o
numbers that are less than one.

Thanks for any assistanc
 
Hi bubba1965!

Use a pre-set custom format
#.000

When pasting the entries use Edit > Paste Special > Formulas
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I understand how to make the custom format under formatting cells an
then custom. But I do not see the Formula option when using Past
Special.

Will this method also get rid of the zero before decimal points so tha
for example 0.4 will come in as .400.

My other concern is that I only want to use decimal points on th
numbers that are being pasted that already have decimal points in them
not all numbers.

For example, I may be pasting years, like 2004 and I obviously do no
want 2004.000, just 2004. I also may be pasting whole numbers like 1
and do not want 15.000

I am trying to calcuate career sports statistic for various people, s
each row will have a year and then several categories worth o
statistics, some with decimal points, some without. For example
batting average is displayed as .300, not 0.3. And home runs i
displayed as 142 and not 142.000.

I hope I am making sense.

Is there any way to paste this information into excel and achieve th
desired formatting or will I have to format each of the rows and cell
after I have pasted the info. I was hoping there was a way of doin
this so I could just paste the info (with paste special, or whatever
and achieve the desired results.

I think I could format the cells as text before I paste and then the
will come in as I want them. However, then I would not be able t
perform calculations or apply formulas to them, which would defeat m
purpose.

Any insight into what method would be best would greatly b
appreciated. Thank
 
Hi bubba!

Edit > Paste Special
Check "Formulas"
OK

It's been available since Excel 97 if not before.

#.000 will give you .400 not 0.400

I'm afraid that without VBA you can't format selectively those numbers
with decimal points and those without. If they are all mixed in one
row or column you might use a helper formula

Example with data all in column A
=MOD(A1,1)<>0
Copy down

This will return TRUE for numbers with a decimal component. Sort on
the helper column and you can then change the format for those with a
decimal component. Before sorting, though, make sure that you have a
consecutive series of record entries so that you can re-sort back to
the original order afterwards. Always save a backup before sorting is
also a good tip.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
OK, I must be doing something wrong.

I create a custom format with #.000

and then I go to Paste Special and the only options I see are Text an
Unicode Text.

I do not see Formula as an option. I am using Excel XP. What am I doin
wrong?


Is there a way by using a macro that I could perform this operation i
on every sheet in my workbook - the same two columns (columns G and I
are the only two columns that use decimal points. The other columns,
would just need whole numbers. Any help with that sort of macro woul
greatly be appreciated.
Thank
 
Paste Special/Formulas only works if the data was copied within XL -
e.g., from other cells. Copying from another application will give you
the options you cite.
 
Back
Top