Extract Numbers and prefix letter

[aromaveda]

Hi All,

I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this?? Thanks a bunch in advance.

SD

[aromaveda]

Normally I see everyone's issues responded by someone, this is my second thread that hasnt been replied to. Am I doing something wrong or are my questions too complicated or already answered in the past? Since I have newly posted the threads i would like to know.
thanks

[ConneXionLost]

Hi aromaveda,

I don't think the netizens here are trying to avoid you, but many of them do not (will not?) own Excel 2007. Unless it is necessary to do so, if you were to save your files with an "xls" extension as opposed to an "xlsx" or "xlsm" you may reach a larger audience. Or, at least, more 'zens who'll be able to view your attachments.

Cheers,

[royUK]

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]

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]

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

[aromaveda]

it should ideally show = "SV29176001045/232034"

Thanks
Sanjay

[martindwilson]

try =TRIM(RIGHT(SUBSTITUTE(A2,":",REPT(" ",30)),20))
edit ah no thats not quite it
ok these seem to be the main variations
1000:1027
5400:5200:5210
FRUITS:14000:14020
GRECO & SONS:66003
MISCELANEOUS:B/L FORM
RV:MUSHROOMS:0053084/231805
SPICES:BULK SPICES 7100:9063B
SPICES:BULK SPICES 7100:SPICE BULK 7900:M9041B
TRUCK REPAIRS # 103
RV:ARTICHOKES:9459013/234253/231711VA
what do you want from each of these?

[aromaveda]

Martin,

You are the best, it does exactly what I want. Now my other problem is the formating, i copy down the formula and it still copies value from the last one. I tried reformatting to "number" it doesnt work. Any suggestions? For your question it shoud extract the bold part. Some items such as truck rentals etc i dont care about. i am only worried about the product items and product codes within that, truck repair etc are the service item codes.

1000:1027
5400:5200:5210
FRUITS:14000:14020
GRECO & SONS:66003
MISCELANEOUS:B/L FORM ---- no extraction
RV:MUSHROOMS:0053084/231805
SPICES:BULK SPICES 7100:9063B or it can extract both segments 7100:9063B I am fine with either
SPICES:BULK SPICES 7100:SPICE BULK 7900:M9041B
TRUCK REPAIRS # 103 not required
RV:ARTICHOKES:9459013/234253/231711VA

Martin thanks a bunch again,

Sanjay

[martindwilson]

is your spead sheet set to manual calculation? ithat would give you the symptoms you describe. what hapens if you press f9?