Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

The example in this section describes a megaformula that determines whether a credit card

number is a valid number.

The basic formulas

Figure 20-6 shows a worksheet set up to analyze a credit card number and determine its validity.

This workbook uses quite a few formulas to make the determination.

Figure 20-6:
The formulas in this worksheet determine the validity of a credit card number.

You can access the credit card number validation workbook on the companion

CD-ROM. The file is named
credit card validation.xlsx
.

In this worksheet, the credit card number is entered in cell F1, with no spaces or hyphens. The

formula in cell F2 follows. This formula appends leading zeros, if necessary, to make the card

number exactly 16 digits long. The other formulas use the string in cell F2.

=REPT(“0”,16–LEN(F1))&F1

When entering a credit card number that contains more than 15 digits, you must be

careful that Excel does not round the number to 15 digits. You can precede the number

with an apostrophe or preformat the cell as Text (using Home➜Number➜Number

Format➜Text).