Parsing for Each Row

Hi All,

I want to parse out a specific data out of this cell in each row which is delimited by comma but the data will never be aligned or separated using to text to column tool. I was hoping it can be done with REG EX by parsing out since the data will always start with , BU-007-WFBA1, and end with the comma. The data should be placed in a new column or field called business unit.

Yellow1, Red3, Main Column, Sun Drive, 013, BU-007-WFBA1, TUFF123, ITEM-OPAC, RTF1918 10, STUN!

Main Column, West Drive, 113, BU-003-WTA1, TUFF133, ITEM-OPAC, RTF0012 10, TY122, TOPPIN1

Sub Column, TUYH, Main Column, East Drive, 123, BU-012-WFBA3, TUFF123, ITEM-OPAC, RTF1918 10, 0SLOPE

Main Column, Sun Drive, 013, BU-007-WFBA1, TUFF123, ITEM-OPAC, RTF1918 10, FAX123

Globe3, Main Column, Sun Drive, 013, BU-007-WFBA1, TUFF123, ITEM-OPAC, RTF1918 10, Earth1

Thank You,

Parse

Transformation

Accepted answers

Inside of a formula tool, this expression will find what you are looking for:

REGEX_Replace([Data], ".*,\sBU-\d\d\d-(.*?),.*", '$1')

Sorry for the exact answer and not having an explanation.

Cheers,

Mark

Another expression:

IIF([Data] == [Result], "Not Found", [Result])

Cheers,

Mark

All comments

What does the result look like?

Result should look like this.

Inside of a formula tool, this expression will find what you are looking for:

REGEX_Replace([Data], ".*,\sBU-\d\d\d-(.*?),.*", '$1')

Sorry for the exact answer and not having an explanation.

Cheers,

Mark

Thank you it worked! One last question now if the data does not contain a ",BU-" can it return "Not Found" in this expression.

Another expression:

IIF([Data] == [Result], "Not Found", [Result])

Cheers,

Mark

Worked like a charm! You earned a subscriber on YouTube. Thank you so much!

Is possible to modified expression to leave the codes - final result should look like 007-WFBA1.

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15867

MarqueeCrew 13708

apathetichell 13703