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!
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!
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!
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
So, I actually have some products that only have one '-' (see screenshot)
not sure how to handle this.
@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.