Extract Character from string

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

Common Use Cases

Accepted answers

philipmannering

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

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

All comments

philipmannering

Hi @SH_94

You can use the Regex Tool for this,

See attached.

Extract Chars.yxmd

philipmannering

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

Extract Chars.yxmd

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.)

Extract Character.PNG

Extract Character.yxmd

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

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}$', '')

录制_2023_09_02_09_07_46_655.gif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703