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

Column1		Result
SSRG POLYL P400E HCG DRST210kg		210kg
VVAA ROLL Z9921 KL22 TREE400LB		400LB
ABC DEFG 333ZZ OKM11 PRESWERK9912KG		9912KG

[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

Column1		Result
SSRG POLYL P400E HCG DRST210kg		210KG
VVAA ROLL Z9921 KL22 TREE400LB		400LB
ABC DEFG 333ZZ OKM11 PRESWERK9912KG		9912KG
CCC 234KDF qwe345kg WTC WHO NSA500		345KG
660LB PORKY 3WIJE O45ONZ BU5HAHA		660LB
GUI99kg StOng 341GG ARMEE HH3400W		99KG

[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:

Column1		Result
SSRG POLYL P400E HCG DRST210kg		210​
VVAA ROLL Z9921 KL22 TREE400LB		181.44​
ABC DEFG 333ZZ OKM11 PRESWERK9912KG		9912​
CCC 234KDF qwe345kg WTC WHO NSA500		345​
660LB PORKY 3WIJE O45ONZ BU5HAHA		299.37​
GUI99kg StOng 341GG ARMEE HH3400W		99​

[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