Formula to pull number before particular charecters ( KG and L

[Ncleveng]

Hello,

I need a formula t pull the Number and Charters for example:

"SSRG POLYL P400E HCG DRST210kg"

I want only the 210KG pulled or if its LB 210LB






Thank you!

 

[jtakw]

Hi,

Is the kg or lb Always at the End of the Text string ?

 

[Ncleveng]

Hi,

Is the kg or lb Always at the End of the Text string ?

99% of the time so I'd be happy with that yes!

 

[sandy666]

if you are able to use PowerQuery not formula try

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SSRG POLYL P400E HCG DRST210kg[/td][td][/td][td=bgcolor:#E2EFDA]210kg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]VVAA ROLL Z9921 KL22 TREE400LB[/td][td][/td][td]400LB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC DEFG 333ZZ OKM11 PRESWERK9912KG[/td][td][/td][td=bgcolor:#E2EFDA]9912KG[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Inserted Last Characters" = Table.AddColumn(#"Extracted Text After Delimiter", "Last Characters", each Text.End([Column1], 2), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Last Characters", "Custom", each Text.Trim([Column1],{"a".."z","A".."Z"})),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Custom", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Result"})
in
    #"Removed Other Columns"[/SIZE]

 

[jtakw]

99% of the time so I'd be happy with that yes!

Perhaps if you show more samples instead of just 1, we may be able to handle the rest...

Try this:

Book1
	A	B
1	SSRG POLYL P400E HCG DRST210kg	210kg
2	SSRG POLYL P400E HCG DRST1045lb	1045lb
Sheet470

Cell Formulas
Range	Formula
B1	=MID(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99),LOOKUP(118,MIN(FIND({0,1,2,3,4}+{0;5},RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)&1/17))),99)

 

[sandy666]

or this,
no matter where is the string with kg or lb in the whole string
condition: kg or lb must have space on the end

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SSRG POLYL P400E HCG DRST210kg[/td][td][/td][td=bgcolor:#E2EFDA]210KG[/td][/tr]

[tr=bgcolor:#FFFFFF][td]VVAA ROLL Z9921 KL22 TREE400LB[/td][td][/td][td]400LB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC DEFG 333ZZ OKM11 PRESWERK9912KG[/td][td][/td][td=bgcolor:#E2EFDA]9912KG[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CCC 234KDF qwe345kg WTC WHO NSA500[/td][td][/td][td]345KG[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]660LB PORKY 3WIJE O45ONZ BU5HAHA[/td][td][/td][td=bgcolor:#E2EFDA]660LB[/td][/tr]

[tr=bgcolor:#FFFFFF][td]GUI99kg StOng 341GG ARMEE HH3400W[/td][td][/td][td]99KG[/td][/tr]
[/table]

[SIZE=1]let
    #"Uppercased Text" = Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table4"]}[Content],{{"Column1", Text.Upper, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Uppercased Text", {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Contains([Column1], "KG") then [Column1] else if Text.Contains([Column1], "LB") then [Column1] else null),
    #"Merged Columns" = Table.CombineColumns(Table.AddColumn(Table.AddColumn(Table.SelectRows(#"Added Conditional Column", each ([Custom] <> null)), "Last Characters", each Text.End([Custom], 2), type text), "Custom.1", each Text.Trim([Custom],{"a".."z","A".."Z"})),{"Custom.1", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Result"})
in
    #"Removed Other Columns"[/SIZE]

 

[Ncleveng]

This worked great! How possible would it be to convert any Lb amounts to KG? If that would be possible I wouldn't need the unit at the end. If its a pain, no issue.

 

[jtakw]

Who are you responding to?

 

[sandy666]

u didn't answer about PowerQuery and to whom u r talkin'
so just for fun here is:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SSRG POLYL P400E HCG DRST210kg[/td][td][/td][td=bgcolor:#E2EFDA]

210​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]VVAA ROLL Z9921 KL22 TREE400LB[/td][td][/td][td]

181.44​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC DEFG 333ZZ OKM11 PRESWERK9912KG[/td][td][/td][td=bgcolor:#E2EFDA]

9912​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CCC 234KDF qwe345kg WTC WHO NSA500[/td][td][/td][td]

345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]660LB PORKY 3WIJE O45ONZ BU5HAHA[/td][td][/td][td=bgcolor:#E2EFDA]

299.37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]GUI99kg StOng 341GG ARMEE HH3400W[/td][td][/td][td]

99​

[/td][/tr]
[/table]

[SIZE=1]let
    #"Uppercased Text" = Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table4"]}[Content],{{"Column1", Text.Upper, type text}}),
    ExtractNumber = Table.TransformColumnTypes(Table.AddColumn(Table.SelectRows(Table.AddColumn(Table.ExpandListColumn(Table.TransformColumns(#"Uppercased Text", {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), "Custom", each if Text.Contains([Column1], "KG") then [Column1] else if Text.Contains([Column1], "LB") then [Column1] else null), each ([Custom] <> null)), "Custom.1", each Text.Trim([Custom],{"A".."Z"})),{{"Custom.1", type number}}),
    Convert2KG = Table.SelectColumns(Table.TransformColumns(Table.TransformColumnTypes(Table.AddColumn(ExtractNumber, "Result", each if Text.Contains([Custom], "LB") then [Custom.1]*0.453592 else [Custom.1]),{{"Result", type number}}),{{"Result", each Number.Round(_, 2), type number}}),{"Result"})
in
    Convert2KG[/SIZE]

result is converted from LB 2 KG (where 1 LB = 0.453592 KG) and rounded to 2 decimals