getting text from the middle of a string

[Sammy22]

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

[atcodedog05 · Accepted solution]

Hi @Sammy22 

Use the below regex in parse to get the columns

Workflow:

Hope this helps : )

[Sebastiaandb · Accepted solution]

Hi @Sammy22 ,

Here you go 🙂

Greetings,

Seb

[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