On Wed, 1 Dec 2004 11:05:04 -0800, "Mike"
wrote:
I need a formula to extract a % from a text string.
Examples of text:
Percent = 12.45% of total
24.56% of parcel
split is 23%
Any help is appreciated.
Thanks,
Mike
It's not clear exactly what you want for a result.
If, on your first sentence, you want as a result the Text String 12.45%, then
the **array-entered** formula:
=MID(LEFT(A1,FIND("%",A1)),MATCH(TRUE,
ISNUMBER(-MID(A1,ROW(INDIRECT(
"1:255")),1)),0),255)
will do that. To **array-enter** a formula, hold down <ctrl<shift while
hitting <enter. Excel will place braces {...} around the formula.
If you want the numeric value 0.1245 (whihch you could then format as a
percent), then the **array-entered** formula:
=--MID(LEFT(A1,FIND("%",A1)),MATCH(TRUE,
ISNUMBER(-MID(A1,ROW(INDIRECT(
"1:255")),1)),0),255)
will do that.
Finally, if you want the text string:
Percent = 12.45 of total
then:
=SUBSTITUTE(A1,"%","")
--ron
|