Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

If you like, you can eliminate the formulas in column B and create a specialized formula that

returns the last name. To do so, substitute the formula in cell B1 for the reference to cell B1 in the

formula. The result is the following array formula:

{=RIGHT(A1,LEN(A1)–(LEN(A1)+1–MATCH(“ “,MID(A1,LEN(A1)+

1–ROW(INDIRECT(“1:”&LEN(A1))),1),0)))}

You must insert parentheses around the formula text copied from cell B1. Without the

parentheses, the formula does not evaluate correctly.

Using a megaformula to determine the

validity of a credit card number

Many people are not aware that you can determine the validity of a credit card number by using

a relatively complex algorithm to analyze the digits of the number. In addition, you can

determine the type of credit card by examining the initial digits and the length of the number. Table

20-4 shows information about four major credit cards.

Table 20-4:
Information about Four Major Credit Cards

Credit Card

Prefix Digits

Total Digits

MasterCard

51–55

16

Visa

4

13 or 16

American Express

34 or 37

15

Discover

6011

16

Validity,
as used here, means whether the credit card number
itself
is a valid number as

determined by the following steps. This technique, of course, cannot determine

whether the number represents an actual credit card account.

You can test the validity of a credit card account number by processing its checksum. All account

numbers used in major credit cards use a Mod 10 check-digit algorithm. The general process is as

follows:

1.
Add leading zeros to the account number to make the total number of digits equal 16.

2.
Beginning with the first digit, double the value of alternate digits of the account number.

If the result is a two-digit number, add the two digits together.

3.
Add the eight values generated in Step 2 to the sum of the skipped digits of the original

number.

4.
If the sum obtained in Step 3 is evenly divisible by 10, the number is a valid credit card