Parse out every other value of concatenated string

[atjhsieh]

I have a dataset with a column that contains long strings of data in this format: 

ABC Inc. | 1234567 | DEF, co. | 8912345 | XYZ co ltd. | 6789123 | 

I would like to break this string up into two separate columns as follows: 

Name	Number
ABC Inc. | DEF, co. | XYZ co ltd.	1234567 | 8912345 | 6789123

Any ideas on what tool/regex formula to use? 

[usmanbashir]

@atjhsieh - please see attached workflow. Hope this helps!

 

[cjaneczko]

Is it always only three groups or are the groupings variable?

[atjhsieh]

Thank you! Sorry, forgot to mention that sometimes the number is blank, but I still want to capture that in the number column. Example below: 

ABC Inc. | 1234567 | DEF, co. |  | XYZ co ltd. | 6789123 | 

Name	Number
ABC Inc. | DEF, co. | XYZ co ltd.	1234567 |  | 6789123

Does that change anything? 

[atjhsieh]

The groupings are variable and sometimes the number is missing (but there should always be a name). I still want to capture the blank number as a segment in the number column so the name and number still line up. 

[usmanbashir · Accepted solution]

@atjhsieh - here's round 2! This should accommodate for the variable groupings if there's a missing value as long as the pipes remain the same. 

[atjhsieh]

Thank you again, this was super helpful!