Finding Customer Accounts from Operations against Sales Accounts that mix L1 and L2

[Scarbrain]

Need a good solution to being able to extract the L1 accounts from a list of sales accounts and also the L2 accounts that are in the same row of data.

L1 accounts are 8 in length, however L2 accounts mostly are 8 in length but can also have a other lengths.

I'm sure there is Tokenize or something on those lines

I would want the matching of both L1 and L2 so I can then lookup the quantities, e.g.

QHA00184-QHB00474 is the operations L1/L2, could we find this in the Sales list and create a column that results in  QHA00184-QHB00474. This Opportunity has a second L1/L2 account, would want that in another column in the same row.

The result is, I will then lookup the quantities from operations against sales quantities.

Note, the Opp #15 they just took the L2 QFB00977 then added in the rest of the last 3 digits (just to make it interesting)

Excel with 2 tabs attached

[Scarbrain · Accepted solution]

Have read other posts, I was probably over complicating it

I can see a pattern, majority L1 and L2 accounts are separated by either / or ,   

I can therefore use Text to Columns and specify ./,.

I can then transpose and join against Op accounts, then will concatenate

Thanks Me haha