i am trying to extract a percentage from a longer string of data, till now i
have been using the following formula:

Data within Cell A3: bunnies 12/18 40% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))

This formula will return the value 40%.

My problem is i am now using percentages with decimals, eg: 40.2%, the above
formula returns:

Data within Cell A3: bunnies 12/18 40.2% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))
..2% is returned.

Unfortunatly the number of digits the percentage value contains is not fixed
(which is what my last formula was based upon) eg: the values 40% 40.1% or
42.52% may appear, not 40.00% or 40.10%

Only one % symbol will appear wihin the string, the percentage value will
always be directly preceded by the % cymbol (i.e. no space between the number
and the symbol), if the percentage is to be a decimal it will only be a
maximum of two decimal places. There will always be a space between the
unwanted data and the first number of the percentage. The 'decimal point' (.)
is not unique to the percentage value within the string

E.g.

12. bunnies 42% abc
1.3 bunnies 42.25% 123
12 ele.-cats 41.3% juka 15

OK, this was a teaser but it worked...

=MID(A3,MAX(ROW(INDIRECT("1:"&LEN(A3)))*(MID(A3,RO W(INDIRECT("1:"&LEN(A3))),1)="
")*(ROW(INDIRECT("1:"&LEN(A3)))<FIND("%",A3)))+1,F IND("%",A3)-MAX(ROW(INDIRECT("1:"&LEN(A3)))*(MID(A3,ROW(INDIRE CT("1:"&LEN(A3))),1)="
")*(ROW(INDIRECT("1:"&LEN(A3)))<FIND("%",A3))) )

This is an array formula, i.e. you need to confirm it with
Shift+Ctrl+Enter. It assumes that there will be a space before the
percentage starts. It also assumes that you have one percentage in the
string or that you want to extract the first percentage.

HTH
Kostis Vezerides

Assuming your string is always in the same format,,

=MID(A3,FIND(" ",A3,FIND(" ",A3,1)+1),FIND("%",A3,1)-FIND(" ",A3,FIND("
",A3,1)+1)+1)

Vaya con Dios,
Chuck, CABGx3



"bobadigilatis" wrote:

i am trying to extract a percentage from a longer string of data, till now i
have been using the following formula:

Data within Cell A3: bunnies 12/18 40% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))

This formula will return the value 40%.

My problem is i am now using percentages with decimals, eg: 40.2%, the above
formula returns:

Data within Cell A3: bunnies 12/18 40.2% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))
.2% is returned.

Unfortunatly the number of digits the percentage value contains is not fixed
(which is what my last formula was based upon) eg: the values 40% 40.1% or
42.52% may appear, not 40.00% or 40.10%

Only one % symbol will appear wihin the string, the percentage value will
always be directly preceded by the % cymbol (i.e. no space between the number
and the symbol), if the percentage is to be a decimal it will only be a
maximum of two decimal places. There will always be a space between the
unwanted data and the first number of the percentage. The 'decimal point' (.)
is not unique to the percentage value within the string

E.g.

12. bunnies 42% abc
1.3 bunnies 42.25% 123
12 ele.-cats 41.3% juka 15

Hi,

If all the strings are of a similar format as your examples (i.e., a
percentage part is preceded by a date-like part containing a "/" character),
the following formula might work.

=LEFT(MID(MID(A3,FIND("/",A3)+1,255),FIND("
",MID(A3,FIND("/",A3),255)),255),FIND("%",MID(MID(A3,FIND("/",A3)+1,255),FIND(" ",MID(A3,FIND("/",A3),255)),255)))

Regards,
B. R. Ramachandran

"bobadigilatis" wrote:

i am trying to extract a percentage from a longer string of data, till now i
have been using the following formula:

Data within Cell A3: bunnies 12/18 40% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))

This formula will return the value 40%.

My problem is i am now using percentages with decimals, eg: 40.2%, the above
formula returns:

Data within Cell A3: bunnies 12/18 40.2% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))
.2% is returned.

Unfortunatly the number of digits the percentage value contains is not fixed
(which is what my last formula was based upon) eg: the values 40% 40.1% or
42.52% may appear, not 40.00% or 40.10%

Only one % symbol will appear wihin the string, the percentage value will
always be directly preceded by the % cymbol (i.e. no space between the number
and the symbol), if the percentage is to be a decimal it will only be a
maximum of two decimal places. There will always be a space between the
unwanted data and the first number of the percentage. The 'decimal point' (.)
is not unique to the percentage value within the string

E.g.

12. bunnies 42% abc
1.3 bunnies 42.25% 123
12 ele.-cats 41.3% juka 15

Hi bobadigilatis,

This may be the easiest way:

=itSEARCH(A3,"[^ ]{1,}%",,3)

See:
http://precisioncalc.com/it/itSEARCH.html

The "[^ ]" tells it to look for any character other than a space. The
"{1,}" tells it to repeat that (any character other than an space) any
number of times, though at least once. The "%" looks for "%". As long
as there is a space right before the string you want to retrieve, and
no spaces within the string you want to retrieve, this should work.
With your three examples, it returns:

42%
42.25%
41.3%


itSEARCH is added by my Excel add-in, inspector text. You can download
the free edition of inspector text here and use it as long as you wish:

http://PrecisionCalc.com


Good Luck,


Greg Lovern
http://PrecisionCalc.com
More Power In Excel


bobadigilatis wrote:
i am trying to extract a percentage from a longer string of data, till now i
have been using the following formula:

Data within Cell A3: bunnies 12/18 40% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))

This formula will return the value 40%.

My problem is i am now using percentages with decimals, eg: 40.2%, the above
formula returns:

Data within Cell A3: bunnies 12/18 40.2% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))
.2% is returned.

Unfortunatly the number of digits the percentage value contains is not fixed
(which is what my last formula was based upon) eg: the values 40% 40.1% or
42.52% may appear, not 40.00% or 40.10%

Only one % symbol will appear wihin the string, the percentage value will
always be directly preceded by the % cymbol (i.e. no space between the number
and the symbol), if the percentage is to be a decimal it will only be a
maximum of two decimal places. There will always be a space between the
unwanted data and the first number of the percentage. The 'decimal point' (.)
is not unique to the percentage value within the string

E.g.

12. bunnies 42% abc
1.3 bunnies 42.25% 123
12 ele.-cats 41.3% juka 15

On Fri, 27 Jan 2006 07:56:02 -0800, "bobadigilatis"
wrote:

i am trying to extract a percentage from a longer string of data, till now i
have been using the following formula:

Data within Cell A3: bunnies 12/18 40% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))

This formula will return the value 40%.

My problem is i am now using percentages with decimals, eg: 40.2%, the above
formula returns:

Data within Cell A3: bunnies 12/18 40.2% 22.7 ABCD
=MID(A3,(FIND("%",A3)-2),FIND("%",A3,3)-(FIND("%",A3)-3))
.2% is returned.

Unfortunatly the number of digits the percentage value contains is not fixed
(which is what my last formula was based upon) eg: the values 40% 40.1% or
42.52% may appear, not 40.00% or 40.10%

Only one % symbol will appear wihin the string, the percentage value will
always be directly preceded by the % cymbol (i.e. no space between the number
and the symbol), if the percentage is to be a decimal it will only be a
maximum of two decimal places. There will always be a space between the
unwanted data and the first number of the percentage. The 'decimal point' (.)
is not unique to the percentage value within the string

E.g.

12. bunnies 42% abc
1.3 bunnies 42.25% 123
12 ele.-cats 41.3% juka 15


1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr



Then try this "regular expression" on your strings:

=--REGEX.MID(A1,"\d*\.?\d+%")

It says to look for a sequence that consists of 0 or more digits; optionally
followed by a dot; and then followed by 1 or more digits; and terminated by a %
sign.
--ron