Forum Discussion
How to isolate text or numbers trailing a particular text string
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
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
6 Replies
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)it should pick up the number occuring right after the space after "of"
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 1Vijay_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?
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
