Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extract % from text string
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 |
#2
|
|||
|
|||
Why do you want to extract it?
Yopu can replace using editreplace, find what %, leave replace with blank If you need a formula to extract it use =MID(A1,FIND("%",A1),1) Regards, Peo Sjoblom "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 |
#3
|
|||
|
|||
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 "Peo Sjoblom" wrote: Why do you want to extract it? Yopu can replace using editreplace, find what %, leave replace with blank If you need a formula to extract it use =MID(A1,FIND("%",A1),1) Regards, Peo Sjoblom "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 |
#4
|
|||
|
|||
Hi
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"P ercent = ",""),"of total",""),"of parcel",""),"split is ","")*1 Or 4 times search & replace all to replace all 4 parts of string with "" Arvi Laanemets "Mike" wrote in message ... 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 |
#5
|
|||
|
|||
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 -----Original Message----- 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 "Peo Sjoblom" wrote: Why do you want to extract it? Yopu can replace using editreplace, find what %, leave replace with blank If you need a formula to extract it use =MID(A1,FIND("%",A1),1) Regards, Peo Sjoblom "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 . |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove all spaces in a text string | Excel Discussion (Misc queries) | |||
Extract hyperlink string from excel cell | Links and Linking in Excel | |||
Formating a text string? | Excel Discussion (Misc queries) | |||
Newbie: How to search a text string from right | Excel Worksheet Functions | |||
How do you extract numbers from a string of chacters in a cell (E. | Excel Worksheet Functions |