How do I find social security numbers in data and replace them with any "Text"

[Neet]

We receives internal company data and we want to do analysis on those data sets, But the problem here is at time the input data we are suppose to utilise has Social Security Number of employee. We want to include a tool in our workflow in order to avoid using any kind of SSN in our data output. So we want to replace the SSN with "None". 

The expected data format is like : 12-2345-6789 or 12/345/6789. 

Please suggest any formula for this one. 

[MarqueeCrew · Accepted solution]

The format replacement might be:

regex_replace([field],"\d{2,3}[-/]?\d{2,4}[-/]?\d{4}",'Tax ID Removed')

this will replace 2-3 numbers followed by 0 or 1 delimiters followed by 2-4 numbers followed by 0-1 delimiters followed by 4 numbers with the supplied text. 

[cpituley]

Hi Neet, 

I may be misunderstanding, but if the SSN shows up in the same column every time you can either

1) Exclude the column entirely if it can only possibly contain the SSN or be blank

2) Write over anything that has a designated character length. You can use the formula tool to say something along the lines of:

If Len(HEADERNAME)=x or len(HEADERNAME) = y then "TEXT" else HEADERNAME end. 

That would work if the only 12 digit data points in that column were SSNs. 

Hope either one of those solutions helps. 

Cheers,

Cameron