Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: Im 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 dont want to copy and past the value (past special - values only), because I want to keep the formula instead of use the value. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a phrase in a string and convert it to a number
On Wed, 24 Sep 2008 02:02:01 -0700, X-Ray
wrote: Im 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 dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert string to number | Excel Worksheet Functions | |||
convert a text string to a number | Excel Discussion (Misc queries) | |||
Convert a number to phrase | Excel Discussion (Misc queries) | |||
Convert a number to a name string | Excel Worksheet Functions | |||
convert string to number | Excel Worksheet Functions |