Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CC number both AmEx and Visa
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CC number both AmEx and Visa
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. -- Regards, Tom Ogilvy "G Henry" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CC number both AmEx and Visa
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "G Henry" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with copy & paste from Visa web site | Excel Worksheet Functions | |||
Cutting and pasting from rows into columns and visa versa. | New Users to Excel | |||
turn a column into a row or visa versa | Excel Discussion (Misc queries) | |||
Conversion of Dec numbers to Bin, Oct and Hex and visa versa | Excel Worksheet Functions | |||
Conversion of Dec numbers to Bin, Oct and Hex and visa versa | Excel Discussion (Misc queries) |