trimming characters based on lengh

[praneshsapmm]

hi ,

I have this requirement of replacing with ' ' or trimming the values of  16 & 17 characters from 23 characters length string . Can someone help on this .

ABCD12345678902023ER001 . 

Value to be trimmed keeps changing and total length of the string also changes.

Any help . 

Thanks

[ShankerV]

Hi @praneshsapmm 

Can you please share sample input and output expected.

[FrederikE]

Hey @praneshsapmm,

"Value to be trimmed keeps changing and total length of the string also changes."

is there some sort of patern to this? Hard to help you, when we don't know what you require. 

[praneshsapmm]

Hi @FrederikE ,

XX0393272882023A7001
YY4661001658912023RE001
ZZ4586805191982023Y8001

Values in bold need to be trimmed . 

Thanks

[binuacs · Accepted solution]

@praneshsapmm One way of doing this

Left([Data],Length([Data])-5)+Right([Data], 3)

[FrederikE]

Hey @praneshsapmm,

That's the pattern I'd guess based on the information: 

This Regex-Code (Language to extract strings) cuts of the 2 characters in front of 001.

REGEX_Replace([Field1], "(.*)..001", "$1$2")

[Towers]

Hi @praneshsapmm,

Here is a way to attack the problem using regex. This method will allow you to remove the two characters no matter what the final 3 numbers are.

REGEX_Replace([field1], "(.*)..(\d{3})", "$1$2")