Extract value from string following/preceding word from defined list - multiple times within one cell (MAC Excel)

[GK007]

Hello Team,

Would really appreciate your help on this (working on MAC):

Example:
Cell contains the following (letters and figures, no particular size or order of the words):
CODE-97 1340 : VARIETY P8039 BATCH Н-15-097/1465/2626709-931 BAGS; VARIETY P8521: BATCH Н-15-097/2030/2661313-166 BAGS, VARIETY P8039 BATCH Н-15-097/1465/2626712 1025 BAGS;

I need to extract product names (usually follows/precedes word "variety", "hybrid", "product", etc.) and corresponding number of bags (usually follows/precedes word "unit", "bag", etc.) into separate cells (ideally into separate rows for every variety/volume combination):
P8039 1956 (note: 1956=931+1025)
P8521 166

Regarding product names:
I can have the list of all potential product names, so product names can be extracted from the string by simply searching any words from my list of products. Important to note that same product name can be mentioned several times within one string/cell with different corresponding volumes.

Regarding volumes:
95% of the time should be whole numbers (no decimals). 95% of the time follows/precedes trigger words such as "bags", "units", etc. My thinking was to search for these trigger words from the list of trigger words and then compare the word/value following and preceding this trigger word and choose the whole number from the two. If there is a simpler way, would be great.

Thanks a lot in advance! This would be a huge help!

 

[FlameRetired]

Hi GK007.

Given the variabilities you've listed it would be helpful and faster if you provided more examples that represent the mix you are working with.

Also include expected results and layout of output you would like ... hand typed if necessary.

20 or 30 rows is usually enough.

You'll likely get more and faster help if you do.

 

[GK007]

Hi GK007.

Given the variabilities you've listed it would be helpful and faster if you provided more examples that represent the mix you are working with.

Also include expected results and layout of output you would like ... hand typed if necessary.

20 or 30 rows is usually enough.

You'll likely get more and faster help if you do.

Sure....here are more examples (please ignore language....looking at my desired output it should still make sense).
Example 2 (cell BT1):
СЕМЕНА ПОДСОЛНЕЧНИКА ДЛЯ ПОСЕВА, ГИБРИД (СОРТ) "НК НЕОМА", ОБРАБОТКА КРУЙЗЕР, ЛОТ USK8E15134А-4, В БУМАЖНЫХ МЕШКАХ (ПОСЕВНАЯ ЕДИНИЦА) ПО 150000 СЕМЯН, 368 ПОСЕВНЫХ ЕДИНИЦ СЕМЕНА ПОДСОЛНЕЧНИКА ДЛЯ ПОСЕВА, ГИБРИД (СОРТ) "СИ ЛАСКАЛА", ОБРАБОТКА КРУЙЗЕР, ЛОТ USK8E15150-4, В БУМАЖНЫХ МЕШКАХ (ПОСЕВНАЯ ЕДИНИЦА) ПО 150000 СЕМЯН, 900 ПОСЕВНЫХ ЕДИНИЦ СЕМЕНА ПОДСОЛНЕЧНИКА ДЛЯ ПОСЕВА, ГИБРИД (СОРТ) "СИ ЛАСКАЛА", ОБРАБОТКА КРУЙЗЕР, ЛОТ USK8E15224А-4, В БУМАЖНЫХ МЕШКАХ (ПОСЕВНАЯ ЕДИНИЦА) ПО 150000 СЕМЯН, 300 ПОСЕВНЫХ ЕДИНИЦ

Desired outcome (new sheet within same file if possible, or just inserting rows below if multiple products):
(Cell DI1) НК НЕОМА (Cell DH1) 368
(Cell DI2) СИ ЛАСКАЛА (Cell DH1) 1200. (note: this product is mentioned twice with volumes 900 & 300)


Example 3 (cell BT2):
СОРТ "АНДРОМЕДА КВС"-3360 ПОС.ЕД.,ПАРТИЯ НМ17226, ОБРАБОТКА - THIRAM,THIAMETHOXAM UND TEFLUTHRIN (INTENSIV 1), HYMEXAZOL,ВЕС НЕТТО 9307.200 СОРТ "ОЛЕСИЯ КВС"-2480 ПОС.ЕД.,ПАРТИЯ НМ17186,ОБРАБОТКА - THIRAM,THIAMETHOXAM, HYMEXAZOL,ВЕС НЕТТО 7390.400 КГ . :

Desired outcome (new sheet within same file if possible, or just inserting rows below if multiple products):
(Cell DI2 - unless shifted down by previous rows) АНДРОМЕДА КВС (Cell DH2) 3360
(Cell DI3 - unless shifted down by previous rows) ОЛЕСИЯ КВС (Cell DH3) 2480


Hope this helps, as really other examples would be about the same. The point is that there is no particular order of words, length of words, special characters across all string (at least I could not find).
Thank you!

 

[FlameRetired]

Sorry GK007. I can't read that or discern the patterns. I am afraid I can't help you.

 

[GK007]

Sorry GK007. I can't read that or discern the patterns. I am afraid I can't help you.

If language is an issue, I can "translate" to English. Thanks for the help.