Extract Character from string

[SH_94]

Hi Community,

I would like to ask how do we extract the character below , the pattern of the string is that there will be underscore for each string. The critieria is removing the all the character from the 7th underscore , counting from right.

Input

ANEK_1327d475_3d00_4acc_a63d_aa29cdb08fb3_8_0

ANLH_ae433e42_e16b_43ca_925c_301617fed3e9_9_0

LFA1_936d8b58_8c8b_4e88_af44_694af2623f33_5_0

SKB1_DKFJD_EIEI_f98cc426_b366_4853_8e25_d0342ae6093d_6_0

CSKS_KODK_EJEJEE_EEJEJE_DJDJD_1ec35287_9b35_4e65_a85e_0c63a5b1e5c5_3_0

Output

ANEK

ANLH

LFA1

SKB1_DKFJD_EIEI

CSKS_KODK_EJEJEE_EEJEJE_DJDJD

THnak you in advance

[PhilipMannering]

Hi @SH_94 

You can use the Regex Tool for this,

See attached.

[PhilipMannering]

Here's a non-regex way of doing it.

[ScottLewis]

Non RegEx solution, if you prefer. Flip String, Parse the 7th _, Flip String. 

(The RegEx version is more elegant but a fair number of folks don't want to have to update RegEx tools when things change.)

 

[SH_94]

Hi @PhilipMannering ,

Thank you for the prompt response.

Apologies that the input is as below after running your workflow

Input

ANEK_1327d475_3d00_4acc_a63d_aa29cdb08fb3_8_0.csv

ANLH_ae433e42_e16b_43ca_925c_301617fed3e9_9_0.xlsx

LFA1_936d8b58_8c8b_4e88_af44_694af2623f33_5_0.yxdb

SKB1_DKFJD_EIEI_f98cc426_b366_4853_8e25_d0342ae6093d_6_0.xls

CSKS_KODK_EJEJEE_EEJEJE_DJDJD_1ec35287_9b35_4e65_a85e_0c63a5b1e5c5_3_0.csv

Output still the same

May i know how to fix the regex in this case?

Thank you

[PhilipMannering · Accepted solution]

Yes I do. Just add a period / fullstop inside the square brackets. This should work,

(_[A-Z0-9.]+){7}$

[jrgo]

To add another alternative (and probably overkill)... since the part you want to remove follow the same pattern, I would target my REGEX to find that pattern then remove it plus anything that shows after to avoid issues should something be added to that string. For example, 

ANEK_1327d475_3d00_4acc_a63d_aa29cdb08fb3_8_0_EDIT_COPY.csv

_\w{8}(?:_\w{4}){3}_\w{12}(?:_\d){2}.*$

[flying008]

Hi, @SH_94 

Formula for field [Output] :

REGEX_Replace([Input], '(_[^_]+?){7}$', '')