Formula to Replace Certain Characters

[mustufa2019]

I am trying ti write a formula that will look for this field CNB_GL_ACCT_CNT from the below table and replace characters after ACCT_ with XXX. For example, the formula will look for CNB_GL_ACCT_CNT and return the field like this CNB_GL_ACCT_XXX.

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_CONFIG_LE

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_CONFIG_LE

CNB_GL_NVISION_REPORT_WRITER

CNB_AP_MANAGER

CNB_AP_SPECIALIST

CNB_AP_VCHR_APPROVER

CNB_GL_ACCT_CNT

CNR_AP_VCHR_APPROVER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_LOC_UPDATE

CNB_AM_MASS_CHANGE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_NVISION_REPORT_WRITER

CNR_AM_VCHR_APPROVER

CNB - Search Asset Management

CNB_AP_MANAGER

CNB_AP_VCHR_APPROVER

CNB_GL_ACCT_CNT

CNR_AP_VCHR_APPROVER

CNB - Search Accounts Payable

CNB_AP_SPECIALIST

CNB - Search Accounts Payable

CNB_GL_ACCT_WM

CNB_GL_JRNL_APPROVER

CNB_GL_NVISION_REPORT_WRITER

CNR_GL_ACCT_CNT

CNR_GL_JRNL_APPROVER

CNR_GL_NVISION_REPORT_WRITER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB - Search General Ledger

CNB - Search Accounts Payable

CNB_AP_SPECIALIST

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_ACCOUNTING_CLERK

CNB_AM_SPECIALIST

CNB_AP_INQUIRY_CNT

CNB_AP_MANAGER

CNB_AP_VCHR_APPROVER

CNB_GL_ACCT_CNT

CNB_GL_ACCT_FPA

CNB_GL_CLOSE_CONSOLIDATION

CNB_GL_JRNL_APPROVER

CNB_GL_NVISION_REPORT_WRITER

CNR_GL_ACCT_CNT

CNR_GL_JRNL_APPROVER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_INQUIRY_CNT

CNB_AP_SPECIALIST

CNB_GL_ACCT_CNT

CNB_GL_NVISION_REPORT_WRITER

CNB - Search Asset Management

CNB_AP_CONFIG_LE

CNB_AP_MANAGER

CNB_AP_VCHR_APPROVER

CNB_GL_ACCT_CNT

CNR_AP_VCHR_APPROVER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AP_CONFIG_LE

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_ACCT_FPA

CNB_GL_CONFIG_LE

CNB_GL_JRNL_APPROVER

CNB_GL_NVISION_REPORT_WRITER

CNB_GL_TREE_MANAGER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_ACCOUNTING_CLERK

CNB_AM_CONFIG_LE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_MANAGER

CNB_AP_VCHR_APPROVER

CNB_GL_ACCT_CNT

CNB_GL_ACCT_FPA

CNB_GL_CLOSE_CONSOLIDATION

CNB_GL_CONFIG_LE

CNB_GL_JRNL_APPROVER

CNB_GL_NVISION_REPORT_WRITER

CNB_GL_TREE_MANAGER

CNB_GL_XWALK_UPDATE

CNB - Search Accounts Payable

CNB_AP_SPECIALIST

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_CONFIG_LE

CNB_GL_NVISION_REPORT_WRITER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_LOC_UPDATE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_NVISION_REPORT_WRITER

CNR_AM_VCHR_APPROVER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB_AM_CONFIG_LE

CNB_AM_SPECIALIST

CNB_AM_VCHR_APPROVER

CNB_AP_INQUIRY_CNT

CNB_GL_ACCT_CNT

CNB_GL_CONFIG_LE

CNB_GL_NVISION_REPORT_WRITER

CNB - Search Accounts Payable

CNB - Search Asset Management

CNB - Search General Ledger

CNB_GL_NVISION_REPORT_WRITER

[AbhilashR · Accepted solution]

Hi @mustufa2019, you could use the  RegEx tool and use its Replace Output Method to achieve your desired result. Attached is a sample solution for your reference. The expression itself is:

(?<=ACCT_).*$

Hope this helps!

[Hollingsworth]

Great question, @mustufa2019. However, in future please be mindful when you choose tags for the post. For example, Spatial Analysis and Salesforce are clearly unrelated to this post.

When I search for posts containing a certain tag, it is very frustrating to find poorly tagged questions that waste everyone's time.

Please use appropriate tags for your questions in the future. I understand you may not always know the exact tag to use, but your mindfulness will improve that undoubtedly improve your selection process.

Effectively tagging your questions will ensure that we have an effective Alteryx community and are not burdened by bad search results.

Thanks!

[mustufa2019]

@AbhilashR thank you! this worked perfectly!