Need help with formula please - Stumped

[dab1477]

In column T (Single Units), I have the following formula; =IF(ISNUMBER(SEARCH("*POT*",M9)),SUM(I9*MID(J9,FIND("/",J9,1)-2,2))," ") where cell M is COMM VARIETY (i.e POT RUS, ,etc); cell I is ORDERED QUANTITY (i.e. 800, 200 or 400, etc.); cell J is PRODUCT DESCRIPTION (i.e. Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton . Side Delights Steamables Poly Roll US #1 . ).

The formula works well when I have a single "/" in column J (Product Description). It returns the correct value in Column T. My issue is when there are mulitple "/" in Cell J (Product Description). I want the formula to read only the NUMERIC digits to the left of the "/" (i.e. 12/1.5; I want to use 12 in the formula). I want to ignore the alpha to the left or right of the "/". (I.e. ignore RED/YELLOW or YELLOW/PURPLE). How do I make this work? Stumped!!

Thanks in advance for any direction given.


Col I J K L M NOPQ R S T
(NOPQ = Blank)
Ordered Product SKU Ordered Comm Completed Pack Single
Quantity Description Netweight Variety Order (x) on Line Units
800 10/5 lb. Ahold Russet USA (40034) R400 40000 POT RUS 3 8000
200 10/5 lb. Ahold Red USA (40015) RED284 10000 POT RED 2 2000
400 5/10 lb. GV Russet USA (279636) R442 20000 POT RUS 5 2000
200 10/5 lb. Ahold White USA (40002) W347 10000 POT WHT 5 2000
54 Fingerlings . B Size 12/1.5lb Steam Carton . Side Delights Steamables Steamable US #1 . F186 972 POT FIN 7 648
54 Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton . Side Delights Steamables Poly Roll US #1 . MDLY114 972 POT MDLY 6 #VALUE!
12 Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton Misc. Side Delight Roastable Roastable Tray US #1 . MIX153 144 POT MIX 5 #VALUE!

 

[jorismoerings]

try this:

=MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),IF(RIGHT(MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),2),1)="/",1,2))

 

[Clyde76]

Hi dab1477,

You've probably already considered this, but I'm a huge fan of helper columns when formulas like this are in play, especially when the data you're trying to use is a bit inconsistent. (e.g. the unit quantity / pack in the descriptions are not standardised).

You may be able to get around it with a formula that can do all sorts of amazing error handling, but if your formula works 90% of the time, fixing up a few rows may not be a big issue.

Here is what I have put together:

Just by breaking down your formulas that you've already got, you can create some helper columns that you can manually fix later. That is, you might save time just by looking at the errors and manually putting in the values in the Units/Pack that you want. Also, I'm not sure the purpose of the POT search is, but if you put a helper in for that, you can send your individual units to 0 quite easily.

If you are in a position where you have tons of orders where these errors could come up that it doesn't make sense to manually fix them, the number of unique product descriptions you have would likely be limited. If that's the case, you can just create a list of your unique product descriptions that you need to pull the units/pack from, do the formulas in that lookup list and then use VLOOKUP to pull the Units/pack back from your unique product descriptions into your order rows. That would save you manually editing each order row.

Hope that helps save some time. Unfortunately, with non-standardised text, it can be nearly impossible to break down with just 1 formula.

 

[dab1477]

try this:

=MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),IF(RIGHT(MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),2),1)="/",1,2))

Booom, goes the dynamite! I combined your efforts with pat of the existing formula to account for non"/" text in ordert to return 0 in those cases. It works. Looks terrible, but it works!
=IF(ISNUMBER(SEARCH("*POT*",M2)),MID(J2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J2&"0123456789")),IF(RIGHT(MID(J2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J2&"0123456789")),2),1)="/",1,2))*$I2," ")

Returns correctly in every sample. Thanks a bunch. 3 months stuck and you resolved my issue in a few days.

 

[dab1477]

!

 

[dab1477]

Hi dab1477,

You've probably already considered this, but I'm a huge fan of helper columns when formulas like this are in play, especially when the data you're trying to use is a bit inconsistent. (e.g. the unit quantity / pack in the descriptions are not standardised).

You may be able to get around it with a formula that can do all sorts of amazing error handling, but if your formula works 90% of the time, fixing up a few rows may not be a big issue.

Here is what I have put together:

Just by breaking down your formulas that you've already got, you can create some helper columns that you can manually fix later. That is, you might save time just by looking at the errors and manually putting in the values in the Units/Pack that you want. Also, I'm not sure the purpose of the POT search is, but if you put a helper in for that, you can send your individual units to 0 quite easily.

If you are in a position where you have tons of orders where these errors could come up that it doesn't make sense to manually fix them, the number of unique product descriptions you have would likely be limited. If that's the case, you can just create a list of your unique product descriptions that you need to pull the units/pack from, do the formulas in that lookup list and then use VLOOKUP to pull the Units/pack back from your unique product descriptions into your order rows. That would save you manually editing each order row.

Hope that helps save some time. Unfortunately, with non-standardised text, it can be nearly impossible to break down with just 1 formula.

Thank you. I will use this as a fall back position. My head says that a single cell formula is doable. But then again, I may be crazy! Thank you.