Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Grabbing everything to the right of the second '-'

vmiller
5 - Atom

Hello,

 

I have a field that has products by brand. I would like to split this out to have a 'Brand' field and a 'product field'. The brand is located RIGHT of the second '-'. I am having issues separating these out. See below for current field layout, and desired field outcomes.

 

Current:

product: 201 CANDY - CHECKLANE - ADAMS & BROOKS INC

 

Desired: 

product: 201 CANDY - CHECKLANE

Brand: ADAMS & BROOKS INC

 

There are multiple products and brands within each product, so the number of characters varies. I will need to grab everything to the RIGHT of the second '-' for the Brand column, and everything to the LEFT of the second '-' for the Product column. And then Any idea on how to accomplish this? 

 

Thanks in advance!

 

5 REPLIES 5
Kenda
16 - Nebula
16 - Nebula

Hello @vmiller 

 

Because the number of characters varies and you're more looking for a pattern, I would suggest using RegEx to solve this problem. I actually like to use RegEx in my Formula tools so I would use the following two expressions:

 

Create a Brand field

trim(REGEX_Replace([Product], "(.*)(\-)(.*)(\-)(.*)", "$5"))

 

Edit the Product field

trim(REGEX_Replace([Product], "(.*)(\-)(.*)(\-)(.*)", "$1$2$3"))

 

With these two expressions, you are essentially splitting up your text into groups separated by dashes then only keeping the parts you want for each field.

 

Hope this helps!

 

Kenda_0-1602858754653.png

 

vmiller
5 - Atom

This is awesome!! I am not great at the REGEX function, I definitely need to get a handle on it since I need it so often.

 

I used this function for the product field:

 

LEFT([Product], Length([Product])-(FindString(ReverseString([Product]),"-") +1))

 

BUT it was giving me trailing white space. So, your solution is much more streamlined and doesn't require any cleanup afterwards, which is amazing. 

 

Thank you SO much!

jdunkerley79
ACE Emeritus
ACE Emeritus

As with @Kenda, I'd use Regex (though it is possible to do with find in string but fiddly).

 

Using a Regex tool it can parse to two fields:

(.*?-.*?)\s*-\s*(.*)

 

Parse will split into multiple fields with the marked groups

 

Have attached a sample

vmiller
5 - Atom

So, I actually have some products that only have one '-' (see screenshot)

 

not sure how to handle this. 

VMill_0-1602864573015.png

 

markcurry
12 - Quasar

@vmiller I'm wondering if it's a simple case of taking everything after the last dash, as the Brand, and everything before it as Product?  If it is, then this simple RegEx statement should work.

 

(.*)\s-\s(.*)

 

If not, then you could also count the number of dashes in your field, and have multiple RegEx statements like this...

 

 

IF REGEX_CountMatches([Field1], "-") = 2 THEN
	REGEX_Replace([Field1], "(.*?-.*?)\s*-\s*(.*)", "$1")
ELSE
	REGEX_Replace([Field1], "(.*)\s*-\s*(.*)", "$1")
ENDIF

 

(And the same, for Brand, like in the attached workflow.)

 

Hope that helps.

Labels