getting text from the middle of a string

i have a field with the below format. i want to create 2 additional fields from the field using the red and the green. i have used the left and substring formulae but not getting the result i need.

9900-10 ABC 1300-7/1.4|EXTRATYPE_M|1300_7_1.400
9900-2 ABC 1100-4/0.58|EXTRATYPE_M|1100_4_0.580
234-102 ABC 360-1/1600|TYPE|V36011600
234-13 ABC 6400R/2.5-120|TYPE|V25120

Developer Tools

Accepted answers

atcodedog05

Hi @Sammy22

Use the below regex in parse to get the columns

Workflow:

Hope this helps : )

strings.yxmd

Sebastiaandb

Hi @Sammy22 ,

Here you go 🙂

Greetings,

Seb

solution.yxmd

All comments

atcodedog05

Hi @Sammy22

Use the below regex in parse to get the columns

Workflow:

Hope this helps : )

strings.yxmd

Sebastiaandb

Hi @Sammy22 ,

Here you go 🙂

Greetings,

Seb

solution.yxmd

Sammy22

awesome! can you explain how this works please?!

atcodedog05

Hi @Sammy22

Here is how it works

1st group (\d+-\d+) is numbers-numbers. The bracket is used for grouping and extracting

\s is space

2nd group ([^|]+) is any character except | can be in the 2nd value as | will be used as separator.

| is as it is

.+ is any characters

refer this blog you will get a better understanding https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

Hope this helps : )

Sebastiaandb

@Sammy22 sure:

(\d+-\d+)\s(.+)(\|.*\|.+)

every part between brackets is one group, so in my example i have 3 groups :-).

\d -> digit

+ -> one or more

- -> jusr because you have a - in your first value

So the first group states "one or more digits followed by a - and again one or more digits".

between the first and second there is a space, stated by \s.

the second group is:

. -> any character

+ -> one or more

So one or more of any character

Because the third group starts with the pipe (|) it's easy for regex to know were the second group should stop.

\| -> just a pipe

. -> any character

* -> zero or more

\| -> just a pipe

. -> any character

+ -> one or more

The third group needs to start with a pipe has some characters and has a pipe following those characters, after the pipe it will have some more characters :-).

It's useful to press the + button of the regex tool or inform yourself on regex101.com.

Greetings

Seb

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703