Extract numeric values from an alphanumeric cell based on a criteria
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 range of similar data? Thanks, Hennerby |
Extract numeric values from an alphanumeric cell based on a criteria
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Quote:
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? Hennerby |
Extract numeric values from an alphanumeric cell based on a criteria
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 |
All times are GMT +1. The time now is 06:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com