In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

If second character of a string is 0, replace it with a space

davidnolan
7 - Meteor

Hello,

 

I'm having trouble with a formula formatting my data. I have a large amount of security numbers and if the second character is a 0 it should be replaced with a space, but only the second character should be replaced. It is ok for the security number to contain a 0 after the second character. My attempts seem to replace all instances of a 0 which I am trying to avoid. Basically what I want to happen is the below:

 

Z0T1 =>  Z T1

A0Q0 =>  A Q0

 

I think REGEX could be a possible solution but I am unfamiliar with using it. Any help would be greatly appreciated

 

Thanks

4 REPLIES 4
ShankerV
17 - Castor

Hi @davidnolan 

 

One way of doing this.

 

ShankerV_0-1681898803827.png

 

(.)([0])(.+)

 

ShankerV_0-1681898854610.png

 

 

Many thanks

Shanker V

binuacs
21 - Polaris

@davidnolan The below formula should work

 

If (Substring([Field,1,1])=‘0’,Left([Field],1) +’ ‘+ Substring([Field],2,length([Field]),[Field])

davidnolan
7 - Meteor

Thank you both!

binuacs
21 - Polaris

@davidnolan updated workflow

binuacs_0-1681919797019.png

 

Labels
Top Solution Authors