Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract numbers from imported cell with text and numbers?
does anyone know how to extract the number from an imported cell if the cell
contains both text and numbers? eg. the cell contains "USD 30.79 (March 27, 2007)" but i want to use only 30.79 in a formula in another cell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract numbers from imported cell with text and numbers?
Have a look at Data - Text to columns.
Mike "jyin" wrote: does anyone know how to extract the number from an imported cell if the cell contains both text and numbers? eg. the cell contains "USD 30.79 (March 27, 2007)" but i want to use only 30.79 in a formula in another cell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract numbers from imported cell with text and numbers?
On Wed, 28 Mar 2007 02:24:01 -0700, jyin
wrote: does anyone know how to extract the number from an imported cell if the cell contains both text and numbers? eg. the cell contains "USD 30.79 (March 27, 2007)" but i want to use only 30.79 in a formula in another cell It depends on your format. If your format is like the above, where the number to be extracted is always the first "number value" in the string, then: =LOOKUP(9.99999999999999E+307,--MID( A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1 &"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) will do what you want. If your format variations are more complex, then you will need to post more information. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract numbers from imported cell with text and number
thanks mike! (:
unfortunately, i forgot to add that i need the data to keep refreshing every hour. i tried using 'text to columns' but after splitting, cells are removed from the imported data and cannot be refreshed. "Mike" wrote: Have a look at Data - Text to columns. Mike "jyin" wrote: does anyone know how to extract the number from an imported cell if the cell contains both text and numbers? eg. the cell contains "USD 30.79 (March 27, 2007)" but i want to use only 30.79 in a formula in another cell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Extract numbers from cell with Text and Numbers | New Users to Excel | |||
How do I extract numbers from a cell with both text and numbers? | Excel Worksheet Functions | |||
extract numbers from cell containing text & numbers | Excel Worksheet Functions | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |