Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to delete and replace a character in a specific position in a cell?

turtleth
6 - Meteoroid

Hi there,

 

Hope you all had a wonderful 4th of July.

 

I am trying to solve a problem over here for work. I am trying to delete the numbers after the 4th period and add a "0" in the front of the digits from 1-9. An example would be from

"A.1.1.1.1 to A.1.1.01"  Please see the below for the data information. Any of your inputs will be deeply appreciated. Thank you! 

 

Original Data  
ReferenceCountStatus
A.1.1.1.1 Leadsheet1Open
A.3.1.01 PPLC Policies and Procedures1Open
B.1.2.01 Setting New Accounts in  NetX5551Open
B.1.2.3 Systematic Entitlements to NetX5551Open
B.1.2.10.1 Leadsheet2Open
B.11.1.02 Receivable / Payable Open Items Report1Addressed
B.11.1.2 Receivable / Payable Open Items Report2Open
B.13.1.7 Month-end Statement Reports1Open

 

What I want it to be  
ReferenceCountStatus
A.1.1.01 Leadsheet1Open
A.3.1.01 PPLC Policies and Procedures1Open
B.1.2.01 Setting New Accounts in  NetX5551Open
B.1.2.03 Systematic Entitlements to NetX5551Open
B.1.2.10 Leadsheet2Open
B.11.1.02 Receivable / Payable Open Items Report1Addressed
B.11.1.02 Receivable / Payable Open Items Report2Open
B.13.1.07 Month-end Statement Reports1Open

 

Best,

 

M

2 REPLIES 2
danrh
13 - Pulsar

This should do the trick:

image.png

The RegEx tool splits out the field into four parts (the first section that needs no change, the second section that needs an additional "0", the third section that needs to be removed, and the fourth that needs no change).  The Formula tool adds the "0" and re-combines the fields, and the Select tool gets rid of extra fields.  Give it a go!

turtleth
6 - Meteoroid

Hi Danrh,

 

Thank you so much!!! 

 

It worked like a magic. I have been stuck with this for days and could not figure it out. Again, truly appreciate it!! Hope you have a very nice weekend.

 

Best,

 

M

Labels