Hi!
I am trying to find a way to extract the data withing each parentheses into different columns
For Example
XXXXXXXXX (NJ, USA) - Multiline (4216137)
XXXXX, LLC (USA) (4065310)
XXXXXX (CA, USA) (100.00% owned) - Research and Consulting Services (6458042)
There can be a different number of parentheses within the row (up to 5). I would like my output to look like this:
XXXXXXXXX | NJ, USA | 4216137 | |
XXXXX, LLC | USA | 4065310 | |
XXXXXX | CA, USA | 100.00% owned | 6458042 |
Thank you very much for your help!
Edgar
Solved! Go to Solution.
I wanted to figure this out using Regex :)
The idea here is to parse the data to rows based on the parenthesis, number each occurrence within the string but forcing the final occurrence to equal 5 (per your requirements of "up to 5"), Cross Tab back to rows, and join back to the original data. The Record ID maintains organization, both for the Cross Tab and the Join tools.
Interesting challenge. Hope this helps!
EDIT: Glanced at this again and saw that you wanted the original data to show just what was prior to the first parenthesis. Added another Regex tool to accommodate.
hi @edasilva80,
here another way to solve your problem.
You have to use a regex tool setted on Parse Method and use as Regular Expression (see img1):
.+(\(.+\)).+(\(.+\))
and delete with a replace or with a left(right) method (just let me explain) the brackets.
All the three flow that i created have a text tool, a regex tool, the difference is the last part that delete the brackets in different ways (img3).
- difficult way ad a multi field for change all type (maybe you can use a select tool),
the first multi field delete the first bracket and the second the remaining bracket into the output from the regex (img2).
- left(right) method use a formula that delete with this formula the brackets
IF CONTAINS([_CurrentFieldName_],'RegexOut')
then right(left([_CurrentField_],length([_CurrentField_])-1),length(left([_CurrentField_],length([_CurrentField_])-1))-1)
else [_CurrentField_] endif
- simple way use a formula tool which have 4 formula that delete 1 bracket per formula for 2 column
I like the left(right) method, because it finds automatically the new columns and delete with 1 tool the brackets (if in the future you had another information within '( )' the flow automatically convert it)
PS use a reegx test to help you with the parse