Corey,

A formula approach.

First, this is based upon the data being in A1:An, so adjust all formulae to
suit.

Insert an Excel name (InsertNameDefine...), with a name of pos_array, and
a RefersTo value of
=ROW(INDIRECT("A1:A"&LEN('3'!A1)))

Then in B1, add this formula

=IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1))),p
os_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_arr
ay)),99))

as an array formula, which means that it is committed with Ctrl-Shift-Enter,
and copy down.

You will see that for ON 3127, you already get the end-result, but not for
O/F 20R. So we need to do one more iteration of this. So, copy B1 over to
C1, and then down, the second iteration should get you your end-results.

If you want the end-result as a number, rather than the text that these
formulae produce, use a slight variation in C1

=--(IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1))
),pos_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_
array)),99)))

again as an array formula, and again copy down.

--

HTH



RP
(remove nothere from the email address if mailing direct)


"Corey" wrote in message
...
Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!