Splitting multiple values in cell & automatically running a conversion between units
I've got cells with a range of values and different units.
Here's an example of what I'm looking for..
1 apple (appl) = 3 oranges (org)
14 apples (appl) = 1 bananas (bans)
What I've got:
___A_____________B________
1__Item_________Value
2__Spam________1.25 org
3__Beef_________1.25-1.75 org
4__Chicken______2-2.75 appl
5__Steak________2-2.5 bans
What I'd like to do is split the range of each items value into separate cells, a low and a high. On top of this, I'm trying to convert all of the values into oranges. Sometimes the item values don't have a range, sometimes they do.
What I'm looking for:
____A________B_______________C_________D
1__ITEM______VALUE__________LOW______HIGH
2__Spam______1.25 org________1.25_______
3__Beef_______1.25-1.75 org____1.25______1.75
4__Chicken____2-2.75 appl_______6________8.25
5__Steak______2-2.5 bans_______84_______105
Any help would be greatly appreciated. Thanks in advance!
|