-1

i have some of length like this

A002 30/2Z 0.25 KG/CONE_HS OIM    - I NEED ->0.25 KG
A003 TEX 22272Z 0.50 KG/CONE_PAP3-000 - I NEED ->0.50 KG

the blank is not fix. Thanks for your help.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Vilart
  • 3
  • 2
  • will `KG` always follow by `/`? also does the weight always show in two decimal places (could it be a whole number in some case)? – Terry W Mar 08 '21 at 04:37
  • yes, some time the weight like 1 KG/CONE or 2 lbs/cone or 1000 yds/cone. – Vilart Mar 08 '21 at 06:01
  • if the number is greater than 1000, will it be shown as `1000 KG` or `1,000 KG`? Also, do you need to extract the unit (KG, LBS) or you are good with just the number value without the unit? – Terry W Mar 08 '21 at 06:40
  • Looks like the only consistent part of the strong is "/CONE". Right? – JvdV Mar 08 '21 at 06:44
  • KG AND LBS I CAN SPLIT. Thanks so much Terry W. – Vilart Mar 08 '21 at 08:56

2 Answers2

1

FILTERXML() may be best fit for you.

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[.*0=0]")

enter image description here

  • "<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>" will construct a valid XML string to process by FILTERXML() formula.
  • XPATH parameter //s[.*0=0] will filter only numbers.
  • If you have more than one number in same cell then you may need to specify position of number you want to return.

More about FILTERXML() here from JvdV

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

In B2, formula copied down :

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH("/cone",A2)-1)," ",REPT(" ",99)),199))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10