Replace a number in formula

Is it possible to replace a Number 0-9 and make it a "X"?

Something like

RegexReplace([Last Initial],"0-9","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
555-5555
------------------------------

Find more posts tagged with

English

Monarch

Accepted answers

Ermela

Hi Vaughn,

If you are using Data Prep Studio, you can use the Redact function. Right-click 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: 01-30-2019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula

Is it possible to replace a Number 0-9 and make it a "X"?

Something like

RegexReplace([Last Initial],"0-9","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
555-5555
------------------------------"

All comments

Ermela

Debi

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: 01-30-2019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula

Is it possible to replace a Number 0-9 and make it a "X"?

Something like

RegexReplace([Last Initial],"0-9","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
555-5555
------------------------------
"

Stephen_Smay

Vaughn,

Using Data Prep Studio's built-in 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") or RedactStrikeoutDigits([SSN], "X", 4)

Regards,
Stephen

------------------------------
Stephen Smay
Senior Solutions Architect
Datawatch Corporation
Bedford MA
(978) 275-8230
------------------------------
-------------------------------------------
Original Message:
Sent: 01-30-2019 04:56 PM
From: Vaughn Mitchell
Subject: Replace a number in formula

Is it possible to replace a Number 0-9 and make it a "X"?

Something like

RegexReplace([Last Initial],"0-9","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
555-5555
------------------------------
"