Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using alphanumeric and numeric criteria in same code | Excel Programming | |||
VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
Need to test for alphanumeric value and write numeric values to ce | Excel Worksheet Functions | |||
Extract numeric part of alphanumeric cell | Excel Worksheet Functions | |||
extract numbers from a alphanumeric cell | Excel Worksheet Functions |