Alteryx Designer Discussions

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

Grabbing everything to the right of the second '-'

Highlighted
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!

 

Highlighted
14 - Magnetar
14 - Magnetar

Hello @VMill 

 

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

 

Highlighted
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!

Highlighted
16 - Nebula
16 - Nebula

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

Highlighted
5 - Atom

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

 

not sure how to handle this. 

VMill_0-1602864573015.png

 

Highlighted
11 - Bolide

@VMill 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