Extracting and recognize various characters from cells - Inventory issue

INV.jpg

i am working on a formula which hopefully will extract data from a cell with mixed characters and be used to create a formula to calculate inventory. The cell with mixed characters has two problems i can't figure out. First, the characters in the column vary in number; that is, some cells have 3 characters and some have 7, etc.- this is making many of the formulas that count characters unusable. The 2nd problem is that i need a formula that recognizes a variety of character strings. For example, some of the cells may have "#10 cs" meaning this item is a case of #10 cans. Other cells may have "10#" meaning there are ten pounds of product in that item. I need excel to recognize the difference. Other cells may have "hgal" for half gallon or "gl" or "gal" for gallon. I need excel to recognize the various mixed characters and give them a value which can then be used to calculate cost of inventory. I am thinking i can resolve this by setting up some sort of look up table which matches the various strings of characters, but i am not sure if that will work nor do i know how to do it.

Can you provide some specific examples of actual text strings that you're dealing with?

Please share the worksheet for the desire out put .

Paste in cell V42 with ctrl+shift+enter then drag down:

=0+MID(T42,MATCH(TRUE,ISNUMBER(MID(T42,ROW($1:$99),1)*1),),SUM(N(ISNUMBER(MID(T42,ROW($1:$99),1)*1))))

Assumptions:
- There is only 1 number in each text string
- Text strings are no longer than 99 characters in length