Re: excel - numbers as text
From: Thuferhawat (Thuferhawat_at_discussions.microsoft.com)
Date: 01/24/05
- Next message: Gord Dibben: "Re: Color scheme in templates from Microsoft Office website"
- Previous message: Jason: "Converting Forms to Data Access Pages"
- In reply to: IanRoy: "Re: excel - numbers as text"
- Next in thread: Rob van Gelder: "Re: excel - numbers as text"
- Reply: Rob van Gelder: "Re: excel - numbers as text"
- Reply: IanRoy: "Re: excel - numbers as text"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 24 Jan 2005 10:13:06 -0800
Howdy IanRoy,
The problem with treating the account number as a number is that xl has a
bug that limits it's precision to 15 significant digits. So a 16 digit
number looses its value in the 16th digit. They are changed to a zero. In
an account number that is very significant. Maybe MS should come up with a
new data type "Account Number". I can't believe that I'm the only one with
this problem.
In the mean time. I'll try and make a paste->special->text button.
Thanks again.
"IanRoy" wrote:
> Hi, Thuferhawat;
> That they will never be used as numbers shouldn't, in and of itself, stop
> you from formatting them as numbers, if that will speed up your work. I don't
> know of a way to change Excel's default paste (paste all). I has occurred to
> me (belatedly) that you are copying from outside Excel. You can use the
> keyboard to paste unformatted text from outside Excel as follows: alt
> (selects Menubar), e (opens Edit), s (opens Paste Special), down arrow (paste
> text without formatting), enter: so alt, e, s, down arrow, enter.
> Alternately, you can put a Paste Values button on the Menubar. The following
> works in Excel 2003, I hope in your version as well:
> View> Toolbars> Customize> Commands> Edit
> Find the Paste Values button and drag it to the left of the File menu on the
> Menubar. Drop it there. Close the Customize dialog. Now pressing alt will
> select that button, and pressing enter will paste the contents (without
> formatting) of the clipboard into the selected cell. Whatever formatting is
> already in the cell will control the display of the data. As I noted before,
> if “text” does not give you the results you want, use the “number” format.
> Regards,
> IanRoy.
>
>
> "Thuferhawat" wrote:
>
> > Thanks for your help!
> >
> > These 16 digit numbers are Customer Account numbers and will never be used
> > as numbers. They may as well be alpha characters.
> >
> > Is there a way in xl to change the default paste functionality.
> >
> > Is there a key board shortcut for Paste->special->text?
> >
> > We do this cutting and pasting hundreds of times each day and this bug in xl
> > is very costly.
> >
> > I wonder if the programable button on a 3 button mouse could be programed to
> > do such a thing as paste->special->text
> >
> > "IanRoy" wrote:
> >
> > > Hi, Thuferhawat;
> > > Yes, that is what I would expect. Formatting the target cell beforehand
> > > would have no effect, since Paste will overwrite it. Formatting the source
> > > cell should give you better results.
> > > Here are the results of my experiments:
> > > 1) Enter 16 digit number, copy, paste: scientific.
> > > 2) Format source as text, then enter number, copy, paste: 16 digits and a
> > > warning.
> > > 3) Enter number, format source as text, copy, paste: scientific.
> > > (In all cases formatting the target cell beforehand should have no effect,
> > > because the default paste is paste all, so the source format is pasted as
> > > well, and that is what I found.)
> > > 4) Format source as number, either before or after entering the number,
> > > copy, paste: 16 digits, no error warning. This would seem to be your best
> > > option, unless you need the 16 digit number formatted as text for another
> > > reason, in which case, go with example "2)."
> > > Regards,
> > > IanRoy.
> > >
> > >
> > > "Thuferhawat" wrote:
> > >
> > > > I did format the cell as text before I pasted.
> > > >
> > > > After formating the cell as TEXT I pasted.
> > > >
> > > > Prior to pasting the 16 digit number into the cell I formated the cell as
> > > > text.
> > > >
> > > > I followed these procedures:
> > > > 1. Format the cell as text.
> > > > 2. paste the 16 digit number into the cell
> > > > Result = 16th digit converted to zero, displayed as scientific notation.
> > > >
> > > >
> > > >
> > > >
> > > > "thuferhawat" wrote:
> > > >
> > > > > I did format the cell as text before I did the cut and paste.
> > > > > That is what is so frustrating.
> > > > > I think that the clipboard is giving it a type of numberic and then when xl
> > > > > gets it it must convert that cell to numberic no matter what you've have told
> > > > > it to do.
> > > > >
> > > > > I'll will try to do a paste->special->values but that will slow us down. We
> > > > > do hundreds of these everyday and this bug in xl is costing us.
> > > > >
> > > > > Do you know of a keyboard shortcut for paste->special->values ?
> > > > >
> > > > >
> > > > >
> > > > > "Chip Pearson" wrote:
> > > > >
> > > > > > Format the cell as Text before pasting the data.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Cordially,
> > > > > > Chip Pearson
> > > > > > Microsoft MVP - Excel
> > > > > > Pearson Software Consulting, LLC
> > > > > > www.cpearson.com
> > > > > >
> > > > > >
> > > > > > "Thuferhawat" <Thuferhawat@discussions.microsoft.com> wrote in
> > > > > > message
> > > > > > news:CCCAC05B-CDA2-42B5-9A49-FEFBA06CC73F@microsoft.com...
> > > > > > > how do I keep 16 digit numbers as text in excel?
> > > > > > > I format the cells as text ahead of time but when I cut and
> > > > > > > paste the
> > > > > > > numbers in the last digit is converted to a zero and it is
> > > > > > > displayed in
> > > > > > > scientific notation.
- Next message: Gord Dibben: "Re: Color scheme in templates from Microsoft Office website"
- Previous message: Jason: "Converting Forms to Data Access Pages"
- In reply to: IanRoy: "Re: excel - numbers as text"
- Next in thread: Rob van Gelder: "Re: excel - numbers as text"
- Reply: Rob van Gelder: "Re: excel - numbers as text"
- Reply: IanRoy: "Re: excel - numbers as text"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
