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

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

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

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

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

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

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