Finding Numbers with Cells that also contain letters

A

Adam

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?
 
D

Dave O

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
 
R

RagDyeR

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!
==============================================

Hi
see:
http://www.dicks-blog.com/archives/...ion-with-worksheet-formulas-part-1/trackback/
 
F

Frank Kabel

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))))
 
A

Adam

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?
 
D

Dave Peterson

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.
 
D

Dave Peterson

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