Remove certain words from string field and keep the remaining

Hello All,

I need your help in using right tool to remove certain words from string field. Words are dynamic. Its a 5 letter words each followed by _. I need to remove 8th and 9th word from the string and keep the remaining. Need to remove red highlighted. Its always 8th and 9th word. But string might have more than 50 words as well.

Example string - A1235_CA#VA_DG**U_DR--C_E19BJ_SE#FL_TR-A7_PN@@Z_13JVW_DJKAC

Thank you for the support.

Regards,

Gayathri K

Datasets

Accepted answers

caltang

Like so?

For Gayathri.yxmd

gawa

hi @KGAYATH4 I propose 2 method.

First is static method by using REGEX like this. It's very simple, but if you need change the position to be removed, you need to rephrase REGEX.

REGEX_Replace([Field1], "^([^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_)[^_]{5}_[^_]{5}_(.+)$", "$1$2")

Second is dynamic method by using Text to Column and Tile tool. You just have to specify the position of the words to be removed in Filter tool. It would be much easier to customize WF even if your input data/requirement change in future.

1246542_gawa.yxmd

flying008

Hi, @KGAYATH4

Another dynamic solution of Regex for you,

Spoiler

REGEX_Replace([Txt], '^((?:[^_]{5}_){7})((?:[^_]{5}_){2})', '$1')

RegEx	Txt
Input	A1235_CA#VA_DG**U_DR--C_E19BJ_SE#FL_TR-A7_PN@@Z_13JVW_DJKAC
Output	A1235_CA#VA_DG**U_DR--C_E19BJ_SE#FL_TR-A7_DJKAC

All comments

caltang

Like so?

For Gayathri.yxmd

gawa

hi @KGAYATH4 I propose 2 method.

First is static method by using REGEX like this. It's very simple, but if you need change the position to be removed, you need to rephrase REGEX.

REGEX_Replace([Field1], "^([^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_[^_]{5}_)[^_]{5}_[^_]{5}_(.+)$", "$1$2")

1246542_gawa.yxmd

caltang

Haha! Nice @gawa san! Same thought process for the dynamic portion.

KGAYATH4

Thank you so much. I will try this.

flying008

Hi, @KGAYATH4

Another dynamic solution of Regex for you,

Spoiler

REGEX_Replace([Txt], '^((?:[^_]{5}_){7})((?:[^_]{5}_){2})', '$1')

RegEx	Txt
Input	A1235_CA#VA_DG**U_DR--C_E19BJ_SE#FL_TR-A7_PN@@Z_13JVW_DJKAC
Output	A1235_CA#VA_DG**U_DR--C_E19BJ_SE#FL_TR-A7_DJKAC

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157