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

 

[xenou]

I think you need to be more specific about "needs to look like". Where do you need it to look like x's? Do you still want to keep the original numbers intact?

 

[KrazyAl]

I think you need to be more specific about "needs to look like". Where do you need it to look like x's? Do you still want to keep the original numbers intact?

The original numbers needs to be removed and replaced with X's The "X" is just to represent that there was a number and it has been removed, for privacy issues.

For example:
Original:
My credit card number is 1234 5678 9123 4567

Edited:
My credit card number is XXXX XXXX XXXX XXXX

 

[Micron]

Simplest would be to replace the entire string (if there is anything there) with a string constant of 16 x's as you've shown. Another option would be to loop through the value from left to right and if the character looks like a number, replace it with an x. However, that seems more cumbersome than what is really needed since it would require a small vba function. If either of those solutions sound like something you can use, let us know, and include the name of the field that may/may not contain a credit card number. Indicate which solution.
I'm used to the idea you're on about, but the last four characters are usually retained to provide some connection to the transaction if required. A bunch of x's really doesn't mean anything IMHO, other than to indicate the field is not Null. For that matter, any character (such as *) or word/words would do the same thing.

 

[KrazyAl]

Hi

The field is called "Post Content" and may include numbers that are required like account number, phone number, bill amount etc. therefore replacing all numbersd with X is not ideal. Also if a string matches 16 digits like mentioned previously we will assume it is a credit card number and X it out. We do not need to keep any part of those 16 digits as a reference. The string of numbers maybe placed anywhere in that field, between text.

 

[Micron]

Now I don't know what you want, or maybe I never did. You may have numbers "that are required" so replacing "all numbers with x is not ideal". You also have this/these strings that look like numbers buried within who knows how many characters of text.
It sound like if the field contains 16 numbers that match a credit card format, replace that string with x's and keep the spaces but keep everything else.
Not only is that a (virtually) impossible task, it would not be necessary if you were not mixing phone numbers, credit card numbers, currency amounts and Heaven knows what else, in the same field. That's a great example of what not to do, so unless I have totally misunderstood the situation, I have to bow out.
Sorry.

 

[james_lankford]

read about regular expressions

this is for excel, but will work with Access with just a little tweaking
vba - How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops - Stack Overflow

Excel Regex Tutorial (Regular Expressions) - The Analyst Cave | Excel, VBA, programming and more

so you'll create a function in an Access module

then you write an update query that updates "Post Content" to the return value of your function

 

[mole999]

i believe its possible to do a find / replace code but you must be able to set a record done marker, as you will run the code everytime you access the data

 

[KrazyAl]

[/I][/COLOR]Not only is that a (virtually) impossible task, it would not be necessary if you were not mixing phone numbers, credit card numbers, currency amounts and Heaven knows what else, in the same field.

The form is free text, just like this forum, people can type in anything and may type in a credit card number, or any other number string like a phone number. Which is why a solution to mask these when the data is extracted is required.

 

[mole999]

why won't ####-####-####-#### work as find, then xxxx-xxxx-xxxx-xxxx replace