Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as numbe
How to in one cell reference formula to:
Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a €śnumber formatted as text€ť 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlass Edit/Replace - Find €“ Alt numeric keypad 0160 €“ Replace suggestion to convert after the fact €“ the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want €śtext€ť I also use the same above Substitution formula to extract desired €śtext€ť from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as numbe
Probably a simpler way, but, this will work for you :)
=IF(ISNUMBER(--SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","")),--SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")",""),SUBS TITUTE(MID(H9,FIND("(",H9)+1,255),")","")) -- ** John C ** "M" wrote: How to in one cell reference formula to: Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a €śnumber formatted as text€ť 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlass Edit/Replace - Find €“ Alt numeric keypad 0160 €“ Replace suggestion to convert after the fact €“ the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want €śtext€ť I also use the same above Substitution formula to extract desired €śtext€ť from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as numbe
Ans 1.
Use =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","")*100 i.e. multiply the value you get by 100 to convert the % into number... Ans 2. Will follow if I get one :-) "M" wrote: How to in one cell reference formula to: Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a €śnumber formatted as text€ť 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlass Edit/Replace - Find €“ Alt numeric keypad 0160 €“ Replace suggestion to convert after the fact €“ the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want €śtext€ť I also use the same above Substitution formula to extract desired €śtext€ť from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as n
Ans 2:
=RIGHT(H9,1+LEN(H9)-MAX((MID(H9,ROW($1:$255),1)="(")*ROW($1:$255))) Paste and press CTRL-SHIFT-ENTER "Sheeloo" wrote: Ans 1. Use =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","")*100 i.e. multiply the value you get by 100 to convert the % into number... Ans 2. Will follow if I get one :-) "M" wrote: How to in one cell reference formula to: Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a €śnumber formatted as text€ť 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlass Edit/Replace - Find €“ Alt numeric keypad 0160 €“ Replace suggestion to convert after the fact €“ the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want €śtext€ť I also use the same above Substitution formula to extract desired €śtext€ť from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as n
Sheeloo
:-) IT WORKED!!!!! Like Magic I sometimes spend hours just trying ti figure out the answer you folks make look so simple Thanks M |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as numbe
Question is why you want to convert 4.25% to 425%?
All you need to convert a text 4.25% to a number is to either use VALUE, -- , *1 or +0 -- Regards, Peo Sjoblom "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Ans 1. Use =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","")*100 i.e. multiply the value you get by 100 to convert the % into number... Ans 2. Will follow if I get one :-) "M" wrote: How to in one cell reference formula to: Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a "number formatted as text" 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlas's Edit/Replace - Find - Alt numeric keypad 0160 - Replace suggestion to convert after the fact - the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want "text" I also use the same above Substitution formula to extract desired "text" from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for extracting text formated numbers within ( )'s as numbe
On Thu, 30 Oct 2008 12:02:07 -0700, M wrote:
How to in one cell reference formula to: Extract WORD created text numbers contained within parenthesis as numbers for example: 103.70(4.25%) After importing data from the web into an Excel column (for example cell H9) such as: I use a Peo Sjoblom suggestion: =SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","") To extract and return the number within the parenthesis which is a “number formatted as text” 4.25% Question 1: Is there someway to write the above formula so that it will return the text formatted number as a number? (I gather that any formula would need to be able to remove or replace the non-breaking character in the process. Presently I use Bob Umlas’s Edit/Replace - Find – Alt numeric keypad 0160 – Replace suggestion to convert after the fact – the only thing that seems to work for me in converting text in Excel created from numbers in WORD to numbers i.e. Pete and others have pointed out that this removes the non-breaking space character 0160. How do I / can I incorporate this within the original formula? Question 2: Sometimes I really do want “text” I also use the same above Substitution formula to extract desired “text” from within parenthesis which works great! Example: My String (XYZ) where XYZ is of variable length. To return XYZ or DESIRED TEXT contained with the parenthesis. The (XYZ) parenthesis is always at the right terminal end of the string. BUT: It can be confused by either: My String (something else (XYZ) as well as My String (something else) and (XYZ) My String something else) (XYZ) Is there a way of writing the above SUBSTITUTION formula to eliminate the occasional confusion? Thanks again all, M Given that the string enclosed by parentheses that you want is always at the end of the string, optionally followed by a nbsp, you can use the following to extract it. This formula will ALSO remove the nbsp, eliminating one of your steps: =TRIM(RIGHT(SUBSTITUTE(REPLACE( A1,FIND(")",A1,LEN(A1)-3),9,""),"(",REPT(" ",99)),99)) To convert the string to a number, you can precede the equation with a double unary: =--TRIM(RIGHT(SUBSTITUTE(REPLACE( A1,FIND(")",A1,LEN(A1)-3),9,""),"(",REPT(" ",99)),99)) For a single formula, which will convert the "extract" to a number if it can be, and otherwise return a string: For XL2007: =IFERROR(--TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(")",A1, LEN(A1)-3),9,""),"(",REPT(" ",99)),99)),TRIM(RIGHT(SUBSTITUTE( REPLACE(A1,FIND(")",A1,LEN(A1)-3),9,""),"(",REPT(" ",99)),99))) Earlier versions of XL: =IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(")",A1, LEN(A1)-3),9,""),"(",REPT(" ",99)),99))),TRIM(RIGHT(SUBSTITUTE( REPLACE(A1,FIND(")",A1,LEN(A1)-3),9,""),"(",REPT(" ",99)),99)), --TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(")",A1, LEN(A1)-3),9,""),"(",REPT(" ",99)),99))) --------------------------------------------------- Simpler: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr and use this formula to extract the last parentheses enclosed value from your string: REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1) Again, to test if this extract can be converted to a number, and do so if it can, something like: =IFERROR(--REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1), REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1)) or, for versions prior to 2007: =IF(ISERR(-REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1)), REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1), --REGEX.MID(A1,"(?<=\()[^\)(]+(?=\))",-1)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting numbers from text | Excel Discussion (Misc queries) | |||
Extracting numbers from a text | Excel Discussion (Misc queries) | |||
EXTRACTING NUMBERS FROM A TEXT CELL | New Users to Excel | |||
Average first n numbers in a range (there may be less than n numbe | Excel Discussion (Misc queries) | |||
Cells formated as numbers are calculating like text | Excel Discussion (Misc queries) |