credit card formatting

[tifosi3]

I am trying to format a cell to a credit card format, so the user can enter
only the numbers and have the dashes between each set of four digits auto
populate. I have tried using both 0000-0000-0000-0000 and
####-####-####-####, and each of these puts a zero at the end. Any
suggestions? THanks in advance for your help.

 

[Don Guillett]

PRE format column 3 > click on sheet tab>view code>insert this.
Now when you type in a 16 digit number in col C below row 1 you will get it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 3 Then Exit Sub
Target.Value = Format(Target, "0000 0000 0000 0000")
End Sub

 

[Jason Morin]

You could use Validation. Assuming the data entry col. is
col. A, select col. A, go Data > Validation,
choose "Custom", and in the "Formula" box put:

=AND(LEN($A1)=16,ISNUMBER(1*LEFT($A1,15)),ISNUMBER(1*RIGHT
($A1,15)))

But Validation is not bulletproof. A user can easily
override the Validation by copying/pasting or going
through the Edit > Clear > Clear All.

Format the column as "0000-0000-0000-0000".

HTH
Jason
Atlanta, GA

 

[tifosi3]

What is I am trying use a specific cell?

You could use Validation. Assuming the data entry col. is
col. A, select col. A, go Data > Validation,
choose "Custom", and in the "Formula" box put:

=AND(LEN($A1)=16,ISNUMBER(1*LEFT($A1,15)),ISNUMBER(1*RIGHT
($A1,15)))

But Validation is not bulletproof. A user can easily
override the Validation by copying/pasting or going
through the Edit > Clear > Clear All.

Format the column as "0000-0000-0000-0000".

HTH
Jason
Atlanta, GA

 

[tifosi3]

I tried this, did not seem to work, also I am looking for it to format to a
specific cell. Also I am not even basically proficient in VB, so I am having
trouble following the logic. Though I appreciate your help.

 

[Don Guillett]

Just PRE format the cell as TEXT and follow the instructions to insert the
macro changing the first line from

If Target.Row < 2 Or Target.Column <> 3 Then Exit Sub
to
if target.address<> "$A$2" then exit sub

Be SURE that the a is capitalized to A and that the " " are as shown.

 

[Jason Morin]

Select the cell and follow the steps I gave you. Change
$A1 in the formula to the cell you've chosen.

Jason