How to isolate text or numbers trailing a particular text string

[monojchakrab]

Hey good people,

I am trying to get the pack number from a table as below :

Towards this end, I wrote a code as below, which works part of the way :

if Text.Contains([Title],"pack",Comparer.OrdinalIgnoreCase) and not Text.Contains([Title],")") then Text.End([Title],1) else 1

But where the string "Pack" does not occur at the end of the [Title] string, then it does not return the required number but returns the character which is there at the end of the string.

Is there a way, I can isolate the number coming after the string "pack of", irrespective where this string occurs within the [title]?

Any help much appreciated.

best

[Anonymous]

You can just use "of " and " |" as delimiters, since as you know, 

Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as any

So = Table.AddColumn(PriorStepOrTableName, "Quantity", each Text. Between Delimiters([ColumnName], "of ", " |"), type text)

But listen, it appears that this is a table with one column. But if "Title" is the name of your single table column, then you are good.

--Nate

[monojchakrab]

it should pick up the number occuring right after the space after "of"

[Vijay_A_Verma · Accepted solution]

Test this and let me know if there is any exception. If yes, I will need that data where you get exception

try Number.From(Text.Split(Text.Split([Title],"Pack of"){1}," "){1}) otherwise 1

[monojchakrab]

@Vijay_A_Verma - Thanks for the quick revert.

However , the formula is probably not playing out fully and returning this error :

[Expression.Error] There weren't enough elements in the enumeration to complete the operation.

How can I send you a link for the [Title] Column from the table?

[monojchakrab]

@Vijay_A_Verma - Vijay, I ran it again, and this time it worked. I most certainly used "" instead of " ", by oversight. Thanks a lot.

[Vijay_A_Verma]

What is the expected answer for these scenarios when following appears at the end

Pack of
Pack of 3 x 150gms
Pack of 2 (2 x 150 gms)