Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Is changing my cell contents (XP)

    If I type 4579637981576789 into Excel it gets displayed as 4.58E+15.

    In the formula bar, it shows my cell as 4579637981576780

    I need the exact number stored. This is for credit card numbers (I made up the example, don't worry) and rounding is a BIG problem.

    I know that you can put a tic (') in front of the number to format it as text, but then my users can't copy/paste the value into our billing software unless they want to delete the pasted tic.

    They are lazy and won't do this.

    How can I keep the format of my number but make it copy/paste-able?

    Thanks!!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Is changing my cell contents (XP)

    Excel can ONLY handle 15 NUMBERS since that is ALL the precision it has for numbers. It will ALWAYS truncate the LAST digit if more than 15 are used!

    You must either make it TEXT, with the apostrophe (') or a space( ), or use combinations of 2 numbers to get 16 digits.
    (8 + 8, 12+4, etc)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Is changing my cell contents (XP)

    Thank you for the reply.

    I was afraid of something like that. I am going to try putting a letter at the end of the credit card number, 4579637981576789X, for example, and see if when they paste it, the X is allowed or not. It is my hope that the billing system is trapping for only 16 digits and lets the CC number in but not the X.

    Thanks again!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Is changing my cell contents (XP)

    I tried something else that also seems to work:

    Format the ENTIRE column(s) to TEXT (select entire column - format - cells - number tab TEXT)
    Now you should be able to enter "numbers" but excel will keep it as text, though there is no apostrophe needed!

    Just make sure if someone gets the Scientific notation (x.xxE+15) that they check the format, since it is now NUMERIC and the last digit is lost!

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Is changing my cell contents (XP)

    If you format the source cells as text, and the copy and paste is done default, the format gets carried over, so there shouldn't be any problem. (Formatting cells as text does cause some other minor hassles.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel Is changing my cell contents (XP)

    If you don't mind using another column, how about this...
    Have the users enter the credit card numbers including hard spaces, i.e., <pre>4579 6379 8157 6789</pre>

    in your example. Have a formula in another column:
    <pre>=SUBSTITUTE(A1," ","")</pre>

    The SUBSTITUTE would make it text, although you'd have to check that it's still text after pasting. A side benefit would be that data entry errors might be reduced in that it's easier to spot mistakes in the spaced format than if all 16 digits are shown consecutively.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •