- Number formatted cells in Excel have digits beyond the 15th changed to zeros (reason). This behavior affects the recording of credit card numbers which can be longer than 15 digits. A work around to this behavior in Excel is to format cells that will contain credit card numbers (in advance of entering the data) as strings instead of numbers.
- To display this string in a more user-friendly format I add a helper column that takes the string and chunks it out.
- I do this by testing the length of the string and if it is 16
characters I chunk it out as 4 4 4 4 (Canadian VISA/MC) and if it is 15 digits I chunk it out as 3 6 5 (Canadian Amex).
Can you adapt my worksheet code for VBA to dynamically apply the desired format to the column containing my credit card strings whenever the data changes?