| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min for the column contaning text
I have a column with text cells. I want to find MIN for numbers inside these
text cells that are before " /" or "/". I'm getting the numbers by using the following formula: =IF(ISNUMBER(TRIM(LEFT(I7,FIND(" /",I7)))*1),TRIM(LEFT(I7,FIND(" /",I7)))*1,"") But, I need to get the MIN for the whole column I2:I30 (without using an additional column to get the numbers and after that to find the MIN) as I'm going to refer to this sheet from a different sheet. I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) but of course it's not working. Can anybody help me with it? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
min for the column contaning text
Hi!
I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) Are you entering that formula as an array? It works for me. Enter it using the key combo of CTRL,SHIFT,ENTER. That is, type the formula. Hold down both the CTRL key and the SHIFT key then hit ENTER. Biff "Alex" wrote in message ... I have a column with text cells. I want to find MIN for numbers inside these text cells that are before " /" or "/". I'm getting the numbers by using the following formula: =IF(ISNUMBER(TRIM(LEFT(I7,FIND(" /",I7)))*1),TRIM(LEFT(I7,FIND(" /",I7)))*1,"") But, I need to get the MIN for the whole column I2:I30 (without using an additional column to get the numbers and after that to find the MIN) as I'm going to refer to this sheet from a different sheet. I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) but of course it's not working. Can anybody help me with it? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
min for the column contaning text
Ooops!
I missed this: (it might be one of "those" days!) that are before " /" or "/". Try this: (array entered) =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1),TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1,"")) Biff "Biff" wrote in message ... Hi! I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) Are you entering that formula as an array? It works for me. Enter it using the key combo of CTRL,SHIFT,ENTER. That is, type the formula. Hold down both the CTRL key and the SHIFT key then hit ENTER. Biff "Alex" wrote in message ... I have a column with text cells. I want to find MIN for numbers inside these text cells that are before " /" or "/". I'm getting the numbers by using the following formula: =IF(ISNUMBER(TRIM(LEFT(I7,FIND(" /",I7)))*1),TRIM(LEFT(I7,FIND(" /",I7)))*1,"") But, I need to get the MIN for the whole column I2:I30 (without using an additional column to get the numbers and after that to find the MIN) as I'm going to refer to this sheet from a different sheet. I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) but of course it's not working. Can anybody help me with it? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
min for the column contaning text
Thank you very much, Biff. It's working perfectly.
"Biff" wrote: Ooops! I missed this: (it might be one of "those" days!) that are before " /" or "/". Try this: (array entered) =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1),TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1,"")) Biff "Biff" wrote in message ... Hi! I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) Are you entering that formula as an array? It works for me. Enter it using the key combo of CTRL,SHIFT,ENTER. That is, type the formula. Hold down both the CTRL key and the SHIFT key then hit ENTER. Biff "Alex" wrote in message ... I have a column with text cells. I want to find MIN for numbers inside these text cells that are before " /" or "/". I'm getting the numbers by using the following formula: =IF(ISNUMBER(TRIM(LEFT(I7,FIND(" /",I7)))*1),TRIM(LEFT(I7,FIND(" /",I7)))*1,"") But, I need to get the MIN for the whole column I2:I30 (without using an additional column to get the numbers and after that to find the MIN) as I'm going to refer to this sheet from a different sheet. I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) but of course it's not working. Can anybody help me with it? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
min for the column contaning text
You're welcome. Thanks for the feedback!
Biff "Alex" wrote in message ... Thank you very much, Biff. It's working perfectly. "Biff" wrote: Ooops! I missed this: (it might be one of "those" days!) that are before " /" or "/". Try this: (array entered) =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1),TRIM(LEFT(I2:I30,FIND("/",I2:I30)-1))*1,"")) Biff "Biff" wrote in message ... Hi! I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) Are you entering that formula as an array? It works for me. Enter it using the key combo of CTRL,SHIFT,ENTER. That is, type the formula. Hold down both the CTRL key and the SHIFT key then hit ENTER. Biff "Alex" wrote in message ... I have a column with text cells. I want to find MIN for numbers inside these text cells that are before " /" or "/". I'm getting the numbers by using the following formula: =IF(ISNUMBER(TRIM(LEFT(I7,FIND(" /",I7)))*1),TRIM(LEFT(I7,FIND(" /",I7)))*1,"") But, I need to get the MIN for the whole column I2:I30 (without using an additional column to get the numbers and after that to find the MIN) as I'm going to refer to this sheet from a different sheet. I'm trying =MIN(IF(ISNUMBER(TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1),TRIM(LEFT(I2:I30,FIND(" /",I2:I30)))*1)) but of course it's not working. Can anybody help me with it? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Sort or Filter option? | Excel Worksheet Functions | |||
Linear Mode
