Formatting for credit card number input

[smartgal]

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?

 

[Fred Smith]

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

 

[Jim Thomlinson]

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...

 

[smartgal]

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!!

 

[David Biddulph]

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.

 

[Fred Smith]

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