+ Reply to Thread
Results 1 to 5 of 5

Display Credit Card Number

  1. #1
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346

    Display Credit Card Number

    I want to store credit card numbers ( 16 digit ) in Excel sheet. I would like to have it displayed the way it is printed on the card ( in cluster of 4 digits separated by spaces or dashes eg. xxxx xxxx xxxx xxxx ). I can format the cell as Text and then enter the number with intervening spaces. But I also need to cut and paste these credit card numbers to other application where intervening spaces are not accepted - it accepts only 16 digits with no other characters in between. Thus the cell should store 16 digit number ( as number or text ) without any spaces but then display the same number in the cell with intervening spaces ( or dashes ). I have Office 2007. Pl help.

    Veerkar
    Last edited by avveerkar; 02-11-2011 at 12:33 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Display Credit Card Number

    Apply a custom number format to those cells of:

    0000 0000 0000 0000
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Display Credit Card Number

    This format will give the desired appearance but Excel will not maintain sufficient precision to store all digits of a 16-digit integer as a number. Only the first 15 digits are preserved with the rest being dropped and stored as 0's.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Display Credit Card Number

    One solution is to store the credit card numbers as text with the spaces, then have another column that shows them without spaces for copy & paste purposes. Such as:

    =SUBSTITUTE(A1," ","")

  5. #5
    Forum Contributor
    Join Date
    01-10-2006
    Location
    Ahmedabad, India
    MS-Off Ver
    Office 2000
    Posts
    346

    Thumbs up Re: Display Credit Card Number

    Quote Originally Posted by 6StringJazzer View Post
    One solution is to store the credit card numbers as text with the spaces, then have another column that shows them without spaces for copy & paste purposes. Such as:

    =SUBSTITUTE(A1," ","")
    Dear JBeaucaire, as StringJazzer said the format you suggested will drop the last digit and substitute it with 0. StringJazzer's suggestion of having another column for copying is a good workaround and I can work with it. Thanks a lot.

    Veerkar

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1