Delete zeros from records

[Cfdiaz2103]

Hello guys,

I would like to transform this data:

8000366
8000367
EFT000000000466
EFT000000000467
EFT000000000468
EFT000000000469
EFT000000000470

EFT000000000401

Into this:

8000366
8000367
EFT466
EFT467
EFT468
EFT469
EFT470

EFT401

As you can see, all the records that starts with "EFT" should trim all the zeros that are in the middle of each record, without trimming the 0 that is part of the rest of the number that is placed at the end.

I'd appreciate any kind of support

[Luke_C · Accepted solution]

Hi @Cfdiaz2103 

Here's one way. This formula will check for records starting with EFT, then using a Replace to temporarily remove 'EFT' to facilitate a TrimLeft to remove the 0s without impacting the 0s you want to keep. 

If StartsWith([Field1],'EFT')
Then 'EFT' + TrimLeft(Replace([Field1],'EFT',''),'0')
Else [Field1]
endif

[Cfdiaz2103]

Hi @Luke_C 

That's a good one 😀

Thank you so much.