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

[davidnolan]

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

[ShankerV · Accepted solution]

Hi @davidnolan 

One way of doing this.

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

Many thanks

Shanker V

[binuacs]

@davidnolan The below formula should work

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

[davidnolan]

Thank you both!

[binuacs]

@davidnolan updated workflow