extract last 3 numbers from each concat. value in col. B and add to col. A concat cells

[therealdees]

Greetings,

I'm trying to come up with something that would combine the information of 2 different columns that hold a single or multiple concatened values in each cell.

Column A has the codes referecing the SKU of products, while Column B has the same amount and pattern as Column A, except it references to the Brand code instead (each concatenated SKU has been substituted for the respective brand code).

What I need to do is extract the last 3 numbers of each value in Column B (for me the problem is dealing with multiple concatenated values separated by a ";") and add it to beginning of each separated value in Column A, followed by a "M".


I attached a workbook holding the situation. Any help will be appreciated.

[Fluff13]

How about

Please Login or Register  to view this content.

[therealdees]

It fkin works!!!!

This is great, thanks for the fast reply

Could you explain me how the FILTERXML and the formula as a whole works?

[Fluff13]

The filterxml function basically converts a string into an array, one of the best ways to figure out what a formula is doing is to use the Evaluate Formula button on the formula tab.

[Bo_Ry]

For fixed length, please try at E2

=TEXTJOIN(";",,TEXT(MID(TRIM(C2),SEQUENCE(9,,4,8),3),"000\M")&MID(TRIM(B2),SEQUENCE(9,,,8),6))

This split to 9 SKU

[therealdees]

Alright, thank you a lot for the tips and solution!

[Fluff13]

You're welcome & thanks for the feedback.