CC number both AmEx and Visa

[Guest]

I need to format a column so when users enter the 15 digit American Express
or 16 digit Visa/MC it will display the number in the correct format
visa/MC xxxx-xxxx-xxxx-xxxx
AmEx xxxx-xxxxxx-xxxxx
Also we need to be able to select and copy the data to paste and it must
have no characters except for the numerical digits.

Also how do I format a column for the credit card date (MM/YY)?
Can this be done?
-Gordon

 

[Tom Ogilvy]

Formatting such as you show only works on data stored as numbers. Excel
will only store 15 significant digits, so 16 digit cards can not be
acurately stored as numbers and thus can not be formatted as you show.

Maybe you need to write a macro using the change event to do the formatting
you need - however, the number would have to be stored as a string with the
actual formatting digits physically present. Doesn't sound like there is an
easy built in solution - may require a bit of programming and some special
procedures.

 

[Guest]

As I searched for an answer to a similar problem, and since it is due to the
15 digit precision limit, I came up with an ide , although not to simple,
that would work for creatingthe format display in a single cell.
First, either in 4 preceeding cells or on a sepreate sheet or area, each
cluster of 4 digits gets entered in to one cell. Then using quotation marks
and & you can look up or link to the 4 cells to display it as one formated
number in one cell.
eg.
A1 B1 C1 D1 E1
1234 5678 9012 3456 =A1&" "&B1" "&C1" "&D1

This way E1 will display 1234 5678 9012 3456

By breaking it up in the four cells you may end up having more useful
information with credit cards as well, as the first 4 digits are the card
type or bank, for the most part.
Cheers! Mark