Extract Numbers and prefix letter

**royUK** · 08-21-2009, 01:04 PM

I don't know what your last post was, but looking at your requirements here it seems that there is no consisyency to what you want, numbers are not in the same place within the string, varying lengths etc.

**aromaveda** · 08-21-2009, 01:52 PM

Roy & Conne,

Thanks for your responses. Attached is the revised file saved with excel format supporting from 97-2003.
Yes, there is a variation. My ultimate goal is to do a sumif of Qty received on the inventory sheet (plz see two new sheets now inserted), so if you can suggest some other way to sumif then i dont need to exract the item numbers. please help

thanks
sd

**aromaveda** · 08-21-2009, 05:26 PM

Roy,

I copy pasted one of the formula i found on the forum. Although it does not do completely what i want but extracts all numerics before the "/". For example for original cell value "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034", the formula extracts - "9176001" & it can go upto 10 digits in that segment. Can you think of modifying this formula to pull the letters before 917 & numbers after and with "/" sign? The formula is -
=LOOKUP(9.999999999E+307,--("0"&MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0 123456789")),ROW($1:$30))))+IF(ISNUMBER(SEARCH("½",A3)),0.1,0)
Please let me know.

Thanks
Sanjay