Home |
Search |
Today's Posts |
#1
|
|||
|
|||
credit card formatting
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. |
#2
|
|||
|
|||
PRE format column 3 click on sheet tabview codeinsert 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 -- Don Guillett SalesAid Software "tifosi3" wrote in message ... 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. |
#3
|
|||
|
|||
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 -----Original Message----- 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. . |
#4
|
|||
|
|||
What is I am trying use a specific cell?
"Jason Morin" wrote: 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 -----Original Message----- 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. . |
#5
|
|||
|
|||
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" wrote: PRE format column 3 click on sheet tabview codeinsert 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 -- Don Guillett SalesAid Software "tifosi3" wrote in message ... 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. |
#7
|
|||
|
|||
Select the cell and follow the steps I gave you. Change
$A1 in the formula to the cell you've chosen. Jason -----Original Message----- What is I am trying use a specific cell? "Jason Morin" wrote: 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 -----Original Message----- 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating credit card debt, interest , and payments | Excel Worksheet Functions | |||
How do I perform a MOD-10 credit card check in Excel? | Excel Worksheet Functions | |||
How do I do a MOD-10 Credit Card check in Excel? | Excel Worksheet Functions | |||
Need Budget Template for Bills, expenses, credit card balances wh. | New Users to Excel | |||
finance charge on credit card | Excel Worksheet Functions |