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