Remove certain words from string field and keep the remaining

[KGAYATH4]

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

[caltang · Accepted solution]

Like so?

[gawa · Accepted solution]

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.

[caltang]

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

[KGAYATH4]

Thank you so much. I will try this. 

[flying008 · Accepted solution]

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