| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Frank Kobel
Hi Frank,
Not sure if you remembered this post but I'm having difficulty with the formula you've provided; please see below previous post. -------------------------------------------------------- Hi Frank, This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Adam ----------- Windows 98 + Office Pro 97 |
|
#2
|
|||
|
|||
|
Hi Adam
the formula works fine for me using the following test data: A1 - 123 cat A2 - cat 123 A3 - the 123 cat A4 - cat123 i copied frank's formula into E1:E4 and used ctrl & shift & enter to enter it the only thing i can think of is that your data is not in cell A1 ... or it does not go down the column but across a row. if you still can't get it to work, could you type out three or four examples of your data. Cheers JulieD "Adam" wrote in message ... Hi Frank, Not sure if you remembered this post but I'm having difficulty with the formula you've provided; please see below previous post. -------------------------------------------------------- Hi Frank, This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Adam ----------- Windows 98 + Office Pro 97 |
|
#4
|
|||
|
|||
|
And you have another reply at the original thread.
Adam wrote: Hi Frank, Not sure if you remembered this post but I'm having difficulty with the formula you've provided; please see below previous post. -------------------------------------------------------- Hi Frank, This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Adam ----------- Windows 98 + Office Pro 97 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Define Range with an offset | Excel Discussion (Misc queries) | |||
| Response to Frank: AM-PM Auto formatting | Excel Discussion (Misc queries) | |||
| Calculate months and years | Excel Discussion (Misc queries) | |||
| Message for Frank follow on Q from yesterday | Excel Discussion (Misc queries) | |||
| Workbooks... | Excel Discussion (Misc queries) | |||
Linear Mode
