﻿ Display numbers as credit card numbers - Excel

# Display numbers as credit card numbers

When you type a number that contains more than 15 digits in a worksheet cell, Microsoft Office Excel changes any digits past the fifteenth place to zeros. In addition, Excel displays the number in exponential notation, replacing part of the number with E+n, where E (which signifies exponent) multiplies the preceding number by 10 to the nth power.

If you create a custom number format for a 16-digit credit card number (such as ################ or ####-####-####-####), Excel still changes the last digit to a zero. To successfully display a 16-digit credit card number in full, you must format the number as text.

For security purposes, you can obscure all except the last few digits of a credit card number by using a formula that includes the CONCATENATE, RIGHT, and REPT functions.

## Display credit card numbers in full

1. Select the cell or range of cells that you want to format.

How to select a cell or a range

Tip   To cancel a selection of cells, click any cell on the worksheet.

Tip   You can also select empty cells, and then enter numbers after you format the cells as text. Those numbers will be formatted as text.

2. On the Home tab, click the Dialog Box Launcher next to Number.

3. In the Category box, click Text.

Note   If you don't see the Text option, use the scroll bar to scroll to the end of the list.

Tip   To include other characters (such as dashes) in numbers that are stored as text, you can include them when you type the credit card numbers.

## Display only the last few digits of credit card numbers

For common security measures, you may want to display only the last few digits of a credit card number and replace the rest of the digits with asterisks or other characters. You can do this by using a formula that includes the CONCATENATE, REPT, and RIGHT functions.

The following procedure uses example data to show how you can display only the last four numbers of a credit card number. After you copy the formula to your worksheet, you can adjust it to display your own credit card numbers in a similar manner.

1. Create a blank workbook or worksheet.

2. In this Help article, select the following example data without the row and column headers.

 1 2 3 4
 A B Type Data Credit Card Number 5555-5555-5555-5555 Formula Description (Result) =CONCATENATE(REPT("****-",3), RIGHT(B2,4)) Repeats the "****-" text string three times and combines the result with the last four digits of the credit card number (****-****-****-5555)
1. How to select example data

1. Click in front of the text in cell A1, and then drag the pointer across the cells to select all the text.

2. To copy the selected data, press CTRL+C.

3. In the worksheet, select cell A1.

4. To paste the copied data, press CTRL+V.

5. To switch between viewing the result and viewing the formula that returns the result, on the Formulas tab, in the Formula Auditing group, click Show Formulas.

Keyboard shortcut  You can also press CTRL+` (grave accent).

Note

Applies To: Excel 2007

﻿