Replace a number in formula
Vaughn
Altair Community Member
Is it possible to replace a Number 09 and make it a "X"?
Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555

Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555

0
Best Answer

Hi Vaughn,
If you are using Data Prep Studio, you can use the Redact function. Rightclick on the column you are replacing numbers with Xs then click Redact. It will give different options for removing numbers.
If you are using Classic, you can create a calculated field using this function RegexReplace([column name],'\d','X') or RedactStrikeoutDigits([column name],'X') and hide/remove the original column.
Hope this helps,
Mela

Mela Sarenas
Software Quality Manager
Datawatch Corporation
Bedford MA
+ 1.800.445.3311


Original Message:
Sent: 01302019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula
Is it possible to replace a Number 09 and make it a "X"?
Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555
"0
Answers

Hi Vaughn,
If you are using Data Prep Studio, you can use the Redact function. Rightclick on the column you are replacing numbers with Xs then click Redact. It will give different options for removing numbers.
If you are using Classic, you can create a calculated field using this function RegexReplace([column name],'\d','X') or RedactStrikeoutDigits([column name],'X') and hide/remove the original column.
Hope this helps,
Mela

Mela Sarenas
Software Quality Manager
Datawatch Corporation
Bedford MA
+ 1.800.445.3311


Original Message:
Sent: 01302019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula
Is it possible to replace a Number 09 and make it a "X"?
Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555
"0 
If you do not want to use redact or can't for some reason, you can still use the formula you mentioned. Just nest it 10 times... Like this, but keep adding for the additional numbers you want to replace:
replace(replace(Replace([Last Initial],","1","X"),"2","X"),"3","X")
Good luck!

Debi Rogers


Original Message:
Sent: 01302019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula
Is it possible to replace a Number 09 and make it a "X"?
Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555

"0 
Vaughn,
Using Data Prep Studio's builtin redact function as mentioned by Mela is the most straightforward  redaction was introduced in DPS version 14.3.1.
If you still want or need to do it by formula, with RegexReplace you can use the regex character for 'digit' which is\d
.
The expression would look like this:RegexReplace([Last Initial], "\d", "X")
There is also a formula function called RedactStrikeoutDigits which does the same thing, just without the need to specify \d. It also has the option to keep n number of digits not redacted (e.g. last 4 of SSN.) I am not sure when this function was added to DPS, possibly same time as redact  it looks like this:RedactStrikeoutDigits([Last Initial], "X")
orRedactStrikeoutDigits([SSN], "X", 4)
Regards,
Stephen

Stephen Smay
Senior Solutions Architect
Datawatch Corporation
Bedford MA
(978) 2758230


Original Message:
Sent: 01302019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula
Is it possible to replace a Number 09 and make it a "X"?
Something like
RegexReplace([Last Initial],"09","X")
I know I can do Replace([Last Initial],"1","X")
However, I need to do all numbers.

Vaughn Mitchell
Data Analyst
Avera Health
NA
5555555

"0