Absolutely perplex if Excel can...

[xoenix]

I will try and explain this as best as possible. Cell A1 has a starting balance that represents vacation time remaining. Let's assume that balance is 40.0. Cell B1 has a drop down (VAC 4.0, VAC 8.0). I want cell A1 to update with the remaining balance. If the user selects VAC 4.0, cell A1 should update to 36.0. The trick here is cell B1 must have a text prefix. I cannot simply use #s (multiple PTO codes). So how do I get a formula to recognize VAC 4.0 and translate that into a number? Thoughts?

Thanks in advance to anyone who takes a look.

 

[mikerickson]

Try =0+MID(B1,4,3)