Parse out every other value of concatenated string

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?

Regex

Transformation

Accepted answers

usmanbashir

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

Parse_NameNumber_v2.yxmd

All comments

usmanbashir

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

Parse_NameNumber.yxmd

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:

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

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

Parse_NameNumber_v2.yxmd

atjhsieh

Thank you again, this was super helpful!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703