Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
I have a cell in which the user will input a credit card number and I want it
to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
Excel's maximum precision is 15 digits. So to handle 16 digits, you need to
use text, not numbers. Therefore formatting will not help you. You can format with a formula something like this: =if(however you tell Visa vs Amex,left(a1,4)&" - "&mid(a1,5,4)&" - "&mid(a1,9,4)&" - "&right(a1,4),left(a1,4)&"-"&mid(a1,5,6)&"-"&right(a1,5)) Regards, Fred "smartgal" wrote in message ... I have a cell in which the user will input a credit card number and I want it to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
I have a few issues...
I tried a the following formulas: Amex: =LEFT(A1,4)&"-"&MID(A1,5,6)&"-"&RIGHT(A1,5) MC / Visa: =LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4) Not only does the Amex formatting not carry over, but it drops off the last character and adds a "0" to the end of it, on both Amex and MC/Visa. Also, in the formula, is 'a1' a cell that should be formatted for visa/mc or amex? They are to enter the type of card from a drop down menu in cell 'b12' and enter the cc number in its entirey in to cell 'b13'. I am not sure if they need to be incorporated. (I did a vlookup with the formulas in a hidden row with the proper label, so that is how it pulls it over. ) Thank you!! "Fred Smith" wrote: Excel's maximum precision is 15 digits. So to handle 16 digits, you need to use text, not numbers. Therefore formatting will not help you. You can format with a formula something like this: =if(however you tell Visa vs Amex,left(a1,4)&" - "&mid(a1,5,4)&" - "&mid(a1,9,4)&" - "&right(a1,4),left(a1,4)&"-"&mid(a1,5,6)&"-"&right(a1,5)) Regards, Fred "smartgal" wrote in message ... I have a cell in which the user will input a credit card number and I want it to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
If you are losing digits and getting a zero at the end, you've put the data
in as a number. If you want to keep more than 15 significant figures you must put the data in as text. Either precede with an apostrophe, or format the cell as text *before* you type in the data. -- David Biddulph "smartgal" wrote in message ... I have a few issues... I tried a the following formulas: Amex: =LEFT(A1,4)&"-"&MID(A1,5,6)&"-"&RIGHT(A1,5) MC / Visa: =LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4) Not only does the Amex formatting not carry over, but it drops off the last character and adds a "0" to the end of it, on both Amex and MC/Visa. Also, in the formula, is 'a1' a cell that should be formatted for visa/mc or amex? They are to enter the type of card from a drop down menu in cell 'b12' and enter the cc number in its entirey in to cell 'b13'. I am not sure if they need to be incorporated. (I did a vlookup with the formulas in a hidden row with the proper label, so that is how it pulls it over. ) Thank you!! "Fred Smith" wrote: Excel's maximum precision is 15 digits. So to handle 16 digits, you need to use text, not numbers. Therefore formatting will not help you. You can format with a formula something like this: =if(however you tell Visa vs Amex,left(a1,4)&" - "&mid(a1,5,4)&" - "&mid(a1,9,4)&" - "&right(a1,4),left(a1,4)&"-"&mid(a1,5,6)&"-"&right(a1,5)) Regards, Fred "smartgal" wrote in message ... I have a cell in which the user will input a credit card number and I want it to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
Dear "smartgal",
1. As we stated previously, you need to enter your data as text, not number. Then you won't lose the last digit. 2. When you don't tell us where your data is located, we use A1 as a sample. We expect you'll be able to figure out the appropriate change. If that doesn't work for, you then tell us up front where the data is located. 3. Given your new information, the If statement would look something like: ==if(b12<"Amex",left(b13,4)&" - "&mid(b13,5,4)&" - "&mid(b13,9,4)&" - "&right(b13,4),left(b13,4)&"-"&mid(b13,5,6)&"-"&right(b13,5)) Regards, Fred "smartgal" wrote in message ... I have a few issues... I tried a the following formulas: Amex: =LEFT(A1,4)&"-"&MID(A1,5,6)&"-"&RIGHT(A1,5) MC / Visa: =LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)&"-"&RIGHT(A1,4) Not only does the Amex formatting not carry over, but it drops off the last character and adds a "0" to the end of it, on both Amex and MC/Visa. Also, in the formula, is 'a1' a cell that should be formatted for visa/mc or amex? They are to enter the type of card from a drop down menu in cell 'b12' and enter the cc number in its entirey in to cell 'b13'. I am not sure if they need to be incorporated. (I did a vlookup with the formulas in a hidden row with the proper label, so that is how it pulls it over. ) Thank you!! "Fred Smith" wrote: Excel's maximum precision is 15 digits. So to handle 16 digits, you need to use text, not numbers. Therefore formatting will not help you. You can format with a formula something like this: =if(however you tell Visa vs Amex,left(a1,4)&" - "&mid(a1,5,4)&" - "&mid(a1,9,4)&" - "&right(a1,4),left(a1,4)&"-"&mid(a1,5,6)&"-"&right(a1,5)) Regards, Fred "smartgal" wrote in message ... I have a cell in which the user will input a credit card number and I want it to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting for credit card number input
You are not going to have a whole pile of luck with this one... XL (and your
computer in general) can only handle 15 significant digits. A credit card number is 16 digits long. That means that it can not hold the las digit if your cell is a number. That leaves us with Text which will hold 16 digits but since it is not a number you can not format it using regular formatting. You will need to use formulas to break the text apart and add the hyphens... =left(A1, 4) & "-" & Mid(A1, 5, 6) & "-" & Mid(A1, 10, 6) Use an if formula to determine if you have Visa or MC... -- HTH... Jim Thomlinson "smartgal" wrote: I have a cell in which the user will input a credit card number and I want it to display a format based on the type of card. So Visa / MC should be "#### - #### - #### - ####" but Amex should be "####-######-#####" - the user will choose the type of card in another cell so what's the most effective way to conditionally format the input to display properly based on the card type? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Credit Card #'s with Dashes | Excel Discussion (Misc queries) | |||
Credit Card Number in excel | Excel Discussion (Misc queries) | |||
Credit Card Number | Excel Worksheet Functions | |||
Credit Card Number | Excel Worksheet Functions | |||
credit card formatting | Excel Discussion (Misc queries) |