PDA

View Full Version : Solved: Formula question for extracting numbers



lucas
12-24-2009, 07:38 PM
I"m trying to extract a number after a /
the number I'm trying to extract from looks like this: 940 VA / 3860 VA

and the following formula works nicely:

=--MID(A1,FIND("/",A1)+2,4)

Which returns 3860

I have encountered a problem with some new numbers that look like the following examples which don't have 4 digits after the slash and space and returns an error:
830 VA / 80 VA
from which I would like to see the number 80 returned
or
940 VA / 0 VA
from which I would like to see the number 0 returned

I have tried several things but can't find an easy way to do this.

I have attached a simple spreadsheet example. The formula's are in column B.

Anyone suggest a direction for accomplishing this using a formula?

tpoynton
12-24-2009, 08:19 PM
=--MID(A1,FIND("/",A1)+2,LEN(A1)-FIND("/",A1)-3) ?

lucas
12-24-2009, 11:43 PM
Thank you very much tpoynton. That works just fine.

If I understand this the 3 makes this work on anything up to 4 digits, is that correct?

tpoynton
12-25-2009, 08:28 AM
the -3 is to address the space and VA, so it should work on any # of digits...not tested for that though!

lucas
12-25-2009, 10:01 AM
That makes perfect sense. Thank you very much tpoynton.