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

Hi, Adam-
I know it can be done with VBA, but I'm not sure about a formula. If
you get no answers feel free to contact me at
Cyclezen(at)yahoo(d0t)com. The VBA code is simple and direct.

Dave O

Hi
see:
http://www.dicks-blog.com/archives/2...t-1/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany

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?

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/

--
Regards
Frank Kabel
Frankfurt, Germany

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?

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?

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?

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

And if that doesn't work, post the string you have in A1 that made the formula
fail.

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