Replace a number in formula

Vaughn
Vaughn Altair Community Member
edited January 2019 in Community Q&A
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
------------------------------
Tagged:

Best Answer

  • Ermela
    Ermela
    Altair Employee
    edited January 2019 Answer ✓
    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
    ------------------------------"

Answers

  • Ermela
    Ermela
    Altair Employee
    edited January 2019 Answer ✓
    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
    ------------------------------"
  • Debi
    Debi Altair Community Member
    edited January 2019
    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_22573
    Stephen_22573
    Altair Employee
    edited January 2019
    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
    ------------------------------
    "

Categories