How to remove specific characters at the start and end of a string

[KCT]

I cant find a formula to remove the specific characters at start and end of a string.

Can you assist? Thx

Trade ID                                                                        New Trade ID

82_AE2020102280737096_80737094_20201026_NE         AE2020102280737096_80737094_20201026

82_11289116747_20232331_NE                                      11289116747_20232331

85_DBH-QUR76N_NE                                                    DBH-QUR76N

85_DBH-MAB3ME_NE                                                   DBH-MAB3ME

[BrandonB]

Here you go! Workflow attached

 

 

[MarqueeCrew]

@KCT,

No RegEx:

TrimRight(IIF(left([Trade ID],3) IN ("82_", "83_"),substring([Trade ID],3)),"_NE")

Note:  I didn't test the formula.

Cheers,

Mark

[vizAlter]

Hi @KCT — Simple "Left" and "Right" functions can give you the solution too:

[vizAlter]

@KCT — Also, try this through a RegEx tool with Parse output method, like below:

^\w{2}_(.*)_\w{2}$

[KCT]

Thanks Brandon.

How do I interpret the expression that you suggested .*?_(.*?)_NE

If I have another record of trade id on the input

example 3940OC9105050191012_P:EUR_R:HKD_1_1 and the result will remain 3940OC9105050191012_P:EUR_R:HKD_1_1, can I put a condition within parse to default to Trade ID?

[KCT]

Thanks Mark. There is an error on the formula on Malformed If statement.

[BrandonB]

Instead you might want to use the following 

.*?_(.*)_

The parenthesis indicates a marked group so what this updated formula is saying is to only pull the data between the first and last underscore. My previous formula would only pull data until the last underscore and an NE. Can you try this regular expression and let me know if it works better for you?