ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Formula to Replace Certain Characters

mustufa2019
8 - Asteroid

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
14 - Magnetar
14 - Magnetar

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_).*$

 

AbhilashR_0-1587517713839.png

 

Hope this helps!

Hollingsworth
11 - Bolide
11 - Bolide

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!

John Hollingsworth
Clear Channel Outdoor
mustufa2019
8 - Asteroid

@AbhilashR thank you! this worked perfectly! 

Labels