Re: excel - numbers as text

From: Rob van Gelder (newsgroups_at_nojunkmail-vangelder.co.nz)
Date: 01/24/05 

Date: Tue, 25 Jan 2005 09:40:30 +1300

Indeed - I would format the column as Text because it's an identifier, not a
number for calculations. 

-- 
Rob van Gelder - http://www.vangelder.co.nz/excel
"Thuferhawat" <Thuferhawat@discussions.microsoft.com> wrote in message 
news:9D28D768-C340-49F5-A4E7-EF38F42B2BC9@microsoft.com...
> 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.