Formula for extracting text formated numbers within ( )'s as numbe

[M]

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

 

[John C]

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),")",""),SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")",""))

 

[Sheeloo]

Ans 2:
=RIGHT(H9,1+LEN(H9)-MAX((MID(H9,ROW($1:$255),1)="(")*ROW($1:$255)))
Paste and press CTRL-SHIFT-ENTER

 

[M]

Sheeloo
IT WORKED!!!!! Like Magic
I sometimes spend hours just trying ti figure out the answer you folks make
look so simple
Thanks
M

 

[Peo Sjoblom]

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

 

[Ron Rosenfeld]

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