If second character of a string is 0, replace it with a space
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Help
- Parse
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@davidnolan The below formula should work
If (Substring([Field,1,1])=‘0’,Left([Field],1) +’ ‘+ Substring([Field],2,length([Field]),[Field])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you both!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
