Problem with extracting numeric characters based on a pre-specified list

[monojchakrab]

Hiya good people of Power query,

I probably have reached out with the same problem before and even accepted some of the solutions proferred here. But I am continuing to have the problem since the strings keep on changing.

I have a column of texts, from which I have removed all text characters and now have a column with only numeric characters. But these strings come in various shapes and sizes. A few snippets as below :

There are other types - but you probably get the drift, right?

While these strings look ominously inaccessible, what is common though is that each type contains the pack size as the numeric characters - like 100, 500, 50, 110, 80, 300, 100 etc...

I have a prepared a separate list of all those possible pack sizes (list named as Packlist) in a separate list as below :

My challenge is to extract only those numeric characters when there is a match with the numbers contained in the Packlist else it should return 1...I have tried various routes (inlcuding chatGPT!) but none is able to solve the problem for all the character types.

Is it possible to design a generic code to achieve this? I am somehow not able to get around to using List.Accumulate and List.Generate to solve this, but probably the best solution would involve those functions.

Thanking you all in advance

best regds.,

[Anonymous]

I would find each combination of delimiters between which your values reside, make a separate query that extracts the values based on each of these delimiter combinations, and then combine (append) the tables.  For example, a table like 

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "(", "||"), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

Then another table like

= Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "#|||", "#"), type text}})

the values based on each of these delimiter combinations, and then combine (append) the tables.  For example, a table like 

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "(", "||"), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, ",,", ","), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

Once you are sure you've found all of the different delimiter combinations, then use the GUI Append Tables function to combine all of the tables. Not easy, but I can't think of another way to do it!

--Nate