Find a phrase in a string and convert it to a number
 

I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.

Find a phrase in a string and convert it to a number
 

Hi,

It would have helped to see some sample data and the formula used to extract
it.

say we have this string in A1
qwe1
This formula in B1 extracts the 1 and converts it to a number
=RIGHT(A1,1)*1

However, if you simply use =RIGHT(A1,1) you get the one as text but you can
still do math on it
=B1+8 would still return 9

Mike




"X-Ray" wrote:

I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.

Find a phrase in a string and convert it to a number
 

You can use the double unary minus (--), or you can multiply by 1, or
you can add zero to the result, or you can wrap the formula with
VALUE( ... )

Hope this helps.

Pete

On Sep 24, 10:02*am, X-Ray wrote:
I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.

Find a phrase in a string and convert it to a number
 

=VALUE( string_formula )

Regards,
Stefi

€žX-Ray€� ezt Ã*rta:

I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.

Find a phrase in a string and convert it to a number
 

Calculations are possible, but when comparing, or vlookup, or use in Pivot´s
the nummeric and alfanumeric values are threated differently. So in in a
lookup I won´t find a numeric value when vlooking in textvalue´s.

"Mike H" wrote:

Hi,

It would have helped to see some sample data and the formula used to extract
it.

say we have this string in A1
qwe1
This formula in B1 extracts the 1 and converts it to a number
=RIGHT(A1,1)*1

However, if you simply use =RIGHT(A1,1) you get the one as text but you can
still do math on it
=B1+8 would still return 9

Mike




"X-Ray" wrote:

I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.

Find a phrase in a string and convert it to a number
 

On Wed, 24 Sep 2008 02:02:01 -0700, X-Ray
wrote:

I´m able to find a specific phrase in a string. But when this is a number the
result is still a text cel. What is the worksheet function I can use to
convert the result into a number ? I don´t want to copy and past the value
(past special - values only), because I want to keep the formula instead of
use the value.


It would have helped if you posted your formula, and the version of Excel you
are using, but, in general, something like:

=if(iserr(-(your_formula)),your_formula,--(your_formula))

will convert the result to a real number, if it can be, and leave it unchanged
if it cannot be.
--ron