Find specific lenght of string of numbers in Access

[KrazyAl]

Hello

After unsuccessful searches, I am posting this here. I have a database that may contain on certain occasion credit card information. I need to find these numbers and rename them. So any number that looks like 1234 5678 9123 4567 or 1234-5678-9123-4567 needs to look like xxxx xxxx xxxx xxxx

This string would be in a text field in my databases. therefore might show up anywhere in that field.

A macro would be fine, even a query function/formulas is good. I can even use Excel if that makes it easier

Thank

 

[KrazyAl]

If it is possible to automate a find and replace, I am Ok with that.

Once the data is run and my file extracted (with the numbers removed) the data is destroyed (table is deleted), and new data is updated the following week. This is a weekly database and previous data is always deleted

 

[mole999]

is this to be done in access or excel, i thought it was to be a permanent change in the database

 

[xenou]

With the usual caveats (matching strings that are somewhat variable is always tricky), James is probably correct that regex is a promising solution.

Assuming in Access, here is a function that can go in a standard module. This is meant to replace 16 digit credit card numbers that may have a hyphen or space (or two spaces) between each set of 4 digits. Already this is probably insufficient since some credit cards (I think) actually have 15 digits. So the regex could use a lot of work. You might find a better pattern googling.

But for demonstration.

Public Function XOutCreditCardNumbers(ByVal arg) As String

Dim re As Object


Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .IgnoreCase = True
    .Multiline = True
    .Pattern = "\d{4}( |\-){0,2}\d{4}( |\-){0,2}\d{4}( |\-){0,2}\d{4}( |\-){0,2}"
End With

If re.Test(arg) Then
    arg = re.Replace(arg, "XXXX-XXXX-XXXX-XXXX")
End If

XOutCreditCardNumbers = arg

End Function

Demonstration:

Sub foo()
Dim s As String
Dim t As String
     s = "Excepteur sint occaecat 1234567890123456 cupidatat non proident, sunt in 1234 5678 9012 3456 culpa qui 1234-5678-9012-3456officia deserunt1234  5678 9012  3456 mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipisicing elit."
     t = XOutCreditCardNumbers(s)
     
     Debug.Print s
     Debug.Print t

End Sub

You could use in a query simply like another function:
Select MyField, XOutCreditCardNumbers(MyField) from MyTable