Text to Columns with a Twist

[RobertRath]

Hello,

I have a data set which includes a data output from an external source (Bloomberg) where a series of advisors are listed out in a single cell and are also separated by "#N/A N/A" delimiters.  Please see column R on the "Loans" tab.

You will note that the source data includes advisor type in CAPS.  These include:  

AGNT Agent(s)
BOOK Book Runner(s)
CLAR Co-Lead Arranger(s)
DOC Documentation Agent(s)
FULLLE LNDR Lender(s)
JOBO Joint-Bookrunners
LDAR Lead Arranger(s)
LGAB Legal Advisor(s) to Borrower
LGAL Legal Advisor(s) to Lender
LNDR Lender(s)
MAAR Mandated Arranger(s)
PART Participant(s)
SPNR Private Equity Sponsor(s)
SYND Syndication Agent(s)

I am looking to create a series of columns that summarize the advisor information available by target company.  For example, I am looking for Columns to read 3 deep such as: AGNT 1, AGNT 2, AGNT 3, BOOK 1, BOOK 2, BOOK 3, etc as shown in the "End Goal" tab).

While I have used Excel to break the text to columns (as shown in tabs "Step 3" and "Step 4"), I cannot successfully implement via Join/Union functions.

Can anyone kindly provide some assistance with this task?  

THANK YOU for your consideration as well - happy to elaborate as needed.

Regards,

Bob

[dougperez]

Try to break with "Tokenize" using regex (with the delimiter #N/A N/A) 

[RobertRath]

Thank you Doug.

I have already broken the data into columns in Excel with delimiters - but I cannot "combine" the two sets (aka tabs "Step 3 Identify Role" and "Step 4 Remove Excess".

Essentially I am looking to populate columns with headers based on the advisor categories (AGNT 1, AGNT 2, AGNT 3, etc.)

Thank you for the quick response as well.

[apathetichell]

I had some flashbacks to a previous life and had a bit of fun making this.... I am fairly sure this is either what you want or quite close to it.

.

[apathetichell]

 I also can't promise that the final product is as readable as you may want it to be... I mean there's one loan with a syndicate of like 72 lenders so that's going to look like a mess when you pivot it. So here's the Mike Bloomberg Trio version...

[apathetichell]

Hi! can you check out my second worfklow and let me know if it answers your question. If so - please mark it as a solution.