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