Extract % from text string

[Mike]

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

 

[Peo Sjoblom]

Why do you want to extract it?
Yopu can replace using edit>replace, find what %, leave replace with blank

If you need a formula to extract it use

=MID(A1,FIND("%",A1),1)

Regards,

Peo Sjoblom

 

[Mike]

I need to extract the numerical value:
ie. "12.45%" from - Percent = 12.45% of total
"24.56% from - amount is 24.56% of parcel

Thanks,

Mike

 

[Arvi Laanemets]

Hi

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Percent = ",""),"of
total",""),"of parcel",""),"split is ","")*1
Or 4 times search & replace all to replace all 4 parts of string with ""


Arvi Laanemets

 

[Biff]

Hi!

Here's one way:

=--MID(A12,MIN(SEARCH
({0,1,2,3,4,5,6,7,8,9},A12&"0123456789")),FIND("%",A12)-MIN
(SEARCH({0,1,2,3,4,5,6,7,8,9},A12&"0123456789")))

This will return 12.45 as a numeric value. If you want
the value formatted as a percent, you will have to format
the cell as percent then use this formula:

=--MID(A12,MIN(SEARCH
({0,1,2,3,4,5,6,7,8,9},A12&"0123456789")),FIND("%",A12)-MIN
(SEARCH({0,1,2,3,4,5,6,7,8,9},A12&"0123456789"))+1)

Biff

 

[Ron Rosenfeld]

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