1. 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.
  2. To display this string in a more user-friendly format I add a helper column that takes the string and chunks it out.
  3. 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).

Sample of my table

Formula I use to chunk in helper column

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?

My table after adding requested VBA code

  • Yes it's possible to apply desire format to column or row using VBA! If your problem is only that you want to display 15 digits number which has been converted in Scientific Notation (by default) then Select data, go to Cell Format and apply 0, Excel will convert Scientific Notation into 15 digit value ☺ – Rajesh S Mar 27 '19 at 5:37
  • I can suggest VBA code to convert Scientific Notation values into Number Format even without using your Formula,, confirm through comments will this works for you or not !! – Rajesh S Mar 27 '19 at 6:15
  • We're not here to do your work. Please share the VBa, specifically the relevant section and explain why it isn't working. Give it a go, you may find you complete it... Voting to close as this is too broad and off topic – Dave Mar 27 '19 at 6:38
  • Just curious - why do you want to do this? As someone who's done a LOT of VBA work, I typically suggest using worksheet functions over VBA whenever possible - it is far more resource efficient and typically makes distribution far safer/simpler. There are some things VBA is needed for, but for something like this, the helper column approach is usually what I would recommend. – CBRF23 Mar 27 '19 at 15:29

First, you will definitely need to have text format in your column B. Because otherwise Excel does it's stuff before this code, so the code cannot reliably do the work.

Then, you will have to open VBA window and open the module of the worksheet, which you want to update automatically. Inside this module paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  Dim rIntersect As Range
  On Error GoTo errH
  Application.EnableEvents = False
  Set rIntersect = Intersect(Me.Range("Table1[Text]"), Target)
  If Not rIntersect Is Nothing Then
    For Each c In rIntersect
      If IsNumeric(c.Value) Then
        If Len(c.Value) = 14 Then
          c.Value = Format(c.Value, "@@@ @@@@@@ @@@@@")
        ElseIf Len(c.Value) = 16 Then
          c.Value = Format(c.Value, "@@@@ @@@@ @@@@ @@@@")
        End If
      End If
  End If
  Application.EnableEvents = True
End Sub

(Thanks, @Ron, for directing towards the Format function.)

Third, update the table name Table1 in the code with your actual table name.

Worksheet_Change is the event which fires every time something changes in the worksheet. In this code, we first ensure that something changed exactly in the credit card number column, and only in this case we start checking the length and modifying values.

  • Take a look at the VBA Format function. You can use it to format text patterns and output a text string, maybe easier than with the string manipulations. – Ron Rosenfeld Mar 29 '19 at 1:11
  • @RonRosenfeld - thanks a lot, it really simplifies things. – ZygD Mar 29 '19 at 7:41
  • 1
    @RajeshS, I dreally don't get it. First, In the answer, there is an instruction of 3 steps. If any of the steps is omitted, the user cannot expect reliable results. In other words, cell format cannot be General if the first step was performed. It is a prerequisite for the code to work reliably. Second, I really do not understand why scientific is any different from any numeric format. 16th+ digits are lost - this is the problem, it is not related to scientific format. I would ask for an example if you want to continue the discussion. – ZygD Mar 29 '19 at 8:35
  • 1
    If I add a test for a scientific notation, it will not help. Because if the format auto-changed, the 16th+ digits were already lost. We must do something before that change, not after. And what we do is - change the format to text in order to completely prevent this problem. – ZygD Mar 29 '19 at 8:44
  • 1
    @rajesh-s The digits are formatted as Text. Not Numbers. You lost sight of the problem: Excel conforms to the IEEE 764 specification & stores numbers to 15 digits of precision. Numbers requiring a precision of more than 16 digits cannot be stored as numbers. – Blind Spots Apr 2 '19 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.