Extract numeric values from an alphanumeric cell based on a criteria

[hennerby]

Hi Excel Expert,

I have a list of values in column A of the form:
SF 17296CCH CALCIUM CHL 0.35KG 0.77LB
SF 17155MMA MALIC 5.442KG 11.998LB

in column B for the above examples I would like to get the KG values:
0.35
5.442

Can someone suggest an appropriate formula to achieve this from a rang
of similar data?

Thanks,
Hennerb

 

[Claus Busch]

Hi,

Am Thu, 2 May 2013 18:19:09 +0100 schrieb hennerby:

SF 17296CCH CALCIUM CHL 0.35KG 0.77LB
SF 17155MMA MALIC 5.442KG 11.998LB

try:
=TRIM(SUBSTITUTE(MID(A1,FIND(".",A1)-1,LEN(A1)-FIND("KG",A1)-2),"KG",))


Regards
Claus Busch

 

[hennerby]

Hi,

Am Thu, 2 May 2013 18:19:09 +0100 schrieb hennerby:
-

try:
=TRIM(SUBSTITUTE(MID(A1,FIND(".",A1)-1,LEN(A1)-FIND("KG",A1)-2),"KG",))


Regards
Claus Busch

Hi Claus,

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)

Any suggestions on a fix?

Hennerb

 

[Claus Busch]

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