The -- converts true/falses to 1/0's. The right most negative sign converts the
true/falses to -1/0 and the left changes the sign to +1/0.
But Frank's formula worked for me.
Try putting it all on one line in the formula bar or paste this version (broken
at a different spot):
=--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),
100-SUM(--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1))))
But it's still array entered in one cell.
Adam wrote:
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?
--
Dave Peterson