Formula tool | Replace

Question

ABC_L1_200

ABC_DB_2020

ABC_A_202

So basically this field has _, alpabhates along with_, _along with AlphaNumeric. But the end result should be below

ABC_200 - Removed Alphanumeric

ABC_2020 - Removed Hyphen along with Alphabet(s).

ABC_202 - Removed Hyphen along with Alphabets

Please kindly advice me to write formula to achieve this.

IraWatt · Accepted Answer

Hey @Sarath27,

I'd recomend to use Regex to parse the unwanted text then remove with a formula:

I used this regex:

_(\w+_)

Check out regex101: build, test, and debug regex for more information on how it works :)

Any questions or issues please ask! 
HTH,
Ira

Ladarthure · Accepted Answer

Hi @Sarath27,

there are multiple ways to do it, if I understand well, it's about removing the part in the middle and keep first and last part:

* Use a formula Regex_Replace([field], '(.+)_(.+)_(.+)', '$1_$3')
* it will keep the first and third marked group but it needs to have an _ as a separator
* You could also use a text to columns, set the delimiter as "_" and then combine the colones 1 and 3 you got from using this tool.

binu_acs · Accepted Answer

@Sarath27 one way of doing this