Hi Hennerby,

Am Fri, 3 May 2013 09:58:23 +0100 schrieb hennerby:

The formula appears to work for some cells but not all:

Column A; Column B
SF 17125NAC SOD CITRATE 0.907KG 2LB; 0.9
SF 17009CAA CITRIC ACID 10.68KG 23.55LB; 0.68
SF 17155MMA MALIC ACID 5.442KG 12LB; 5.44
SF 17125NAC SOD CITRATE 6.123KG 13.5LB; 6.123K
SF 17009CAA IS CITRIC 10.68KG 23.55LB; 0.68
SF 17155MMA IS MALIC 5.442KG 12LB; 5.44
SF 17125NAC IS SOD CIT 6.123KG 13.5LB; 6.123K
SF 17009CAA PO CITRIC 10.68KG 23.55LB; 0.68
SF 17155MMA PO MALIC 5.442KG 12LB; 5.44
SF 17125NAC PO SOD CIT 6.123KG 13.5LB; 6.123K
SF 17286ASP 5.613KG(PACK5.766KG) 12.37LB; 5.613(PACK5.766)

try:
=TRIM(MID(A1,FIND(".",A1)-2,IF(FIND("KG",A1)-FIND(".",A1)=4,6,5)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2