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

[X-Ray]

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.

 

[Mike H]

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

 

[Pete_UK]

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

 

[Stefi]

=VALUE( string_formula )

Regards,
Stefi

„X-Ray†ezt írta:

 

[X-Ray]

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.

 

[Ron Rosenfeld]

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