isolating a string containing a % symbol

[bobadigilatis]

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

[vezerid]

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

=MID(A3,MAX(ROW(INDIRECT("1:"&LEN(A3)))*(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)="
")*(ROW(INDIRECT("1:"&LEN(A3)))<FIND("%",A3)))+1,FIND("%",A3)-MAX(ROW(INDIRECT("1:"&LEN(A3)))*(MID(A3,ROW(INDIRECT("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

[CLR]

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
>
>

[B. R.Ramachandran]

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
>
>

[[email protected]]

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

[Ron Rosenfeld]

On Fri, 27 Jan 2006 07:56:02 -0800, "bobadigilatis"
<[email protected]> 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