Parse Numbers

[Kristie_Pires]

Hello! I am trying to parse the data to get the following result:

ID	New Column
500071001255600115	710012556115
500071001255600099	71001255699
500071001255600203	710012556203

I can take out the four first digits with a "Right" string function, but I am not sure how to take out the zeros (only after the ...12556(take out zeroes), with the exception when the last three digits contain a number in the middle. In that case, I want to keep the zero). 

I am new to "Regex" so I don't know how to write an expression that does what I want.

Thank you!! 

[PhilipMannering · Accepted solution]

Not sure if this will work for all your data, but give it a go,

[OllieClarke]

@Kristie_Pires How about this:

REGEX_REPLACE([field],'.*(710012556)0*(.*)','$1$2')

Which removes all consecutive 0s after 710012556, as well as everything before it

edit: Ah I see @PhilipMannering has got there with a better solution :)

[PhilipMannering]

@OllieClarke I was thinking the same thing about your solution <3

[Kristie_Pires]

Thank you @PhilipMannering & @OllieClarke!