This is similar to a post I made yesterday; however, I found more bad data and different syntaxes in the fields that nullifies the solution to it. I have a comma delimited field that follows this syntax: ID, Name, ID, Name. Yes, I have lambasted the person who thought about creating a field like this. But, I can't get to the original data, so I have to break it out to 1 per row where ID and Name are separate fields, but correctly aligned. * The vendor field can have any number of "Vendors" in it. * The syntax is like so "####, aaaaa, ####, aaaa" * The "#" can be any number of digits * The "aaaa" can be any number of alphas * The "aaaa" may have other commas in it The only reliable way to identify the ID from the name is that the comma after the ID is preceded by a digit. I created the attached workflow in an attempt to use REGEX replaces the pattern "#," with a pipe character so I could then split to rows and then cross tab it to the results I'm trying to get, but the split to rows part isn't working. Input PO_IDVendor2222222, Ferrari, INC, 333,McLaren LLC, 444,Toyota5555123, RedBull, INC, 456, Aston LLC Needed Results PO_IDVendorIDVendorName2222222Ferrari,INC2222333McLaren LLC2222444Toyota5555123RedBull, Inc5555456Aston LLC Thanks for any help! sample.yxmd

Hi @csh8428 Try updating the regex to REGEX_Replace([Vendor], '(\b\d+\b|\b[A-Z]+\d+\b)', '|$1|') \b\d+\b → Matches purely numeric vendor IDs (e.g., 222, 333, 444). \b[A-Z]+\d+\b → Matches alphanumeric vendor IDs (e.g., ABC123456).

Help with split to rows

csh8428

This is similar to a post I made yesterday; however, I found more bad data and different syntaxes in the fields that nullifies the solution to it. I have a comma delimited field that follows this syntax: ID, Name, ID, Name. Yes, I have lambasted the person who thought about creating a field like this. But, I can't get to the original data, so I have to break it out to 1 per row where ID and Name are separate fields, but correctly aligned.

The vendor field can have any number of "Vendors" in it.
The syntax is like so "####, aaaaa, ####, aaaa"
The "#" can be any number of digits
The "aaaa" can be any number of alphas
The "aaaa" may have other commas in it

The only reliable way to identify the ID from the name is that the comma after the ID is preceded by a digit. I created the attached workflow in an attempt to use REGEX replaces the pattern "#," with a pipe character so I could then split to rows and then cross tab it to the results I'm trying to get, but the split to rows part isn't working.

Input

PO_ID	Vendor
2222	222, Ferrari, INC, 333,McLaren LLC, 444,Toyota
5555	123, RedBull, INC, 456, Aston LLC

Needed Results

PO_ID	VendorID	VendorName
2222	222	Ferrari,INC
2222	333	McLaren LLC
2222	444	Toyota
5555	123	RedBull, Inc
5555	456	Aston LLC

Thanks for any help!

sample.yxmd

Regex

Parse

Transformation

Accepted answers

Luke_C

Hi @csh8428

Try updating the regex to REGEX_Replace([Vendor], '(\b\d+\b|\b[A-Z]+\d+\b)', '|$1|')

\b\d+\b → Matches purely numeric vendor IDs (e.g., 222, 333, 444).
\b[A-Z]+\d+\b → Matches alphanumeric vendor IDs (e.g., ABC123456).

All comments

Luke_C

Hi @csh8428

You were close, I modified your regex replace a bit, otherwise used most of what you had built:

sample (1).yxmd

Genevieve

Hi @csh8428

I'd suggest you use the split to rows, then a multi row to identify your Vendor ID and summarise to present your data.

Screenshot 2025-02-12 155802.png

Split to rows.yxzp

csh8428

@Luke_C Thanks! It worked for my sample data, but the regex isn't working for all the production data.

Here's an example using anonymized production data

PO_ID	Vendor
2222	ABC123456, AMAZON, INC., DEF67890, Best Buy, INC.

With production data it's also putting a pipe in between an alpha character and a numeric digit.

In this scenario the REGEX is producing this: "ABC|123456|, AMAZON, INC., DEF|67890|, Best Buy INC."; which messes up the downstream tools

Luke_C

Hi @csh8428

Try updating the regex to REGEX_Replace([Vendor], '(\b\d+\b|\b[A-Z]+\d+\b)', '|$1|')

\b\d+\b → Matches purely numeric vendor IDs (e.g., 222, 333, 444).
\b[A-Z]+\d+\b → Matches alphanumeric vendor IDs (e.g., ABC123456).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703