write only numbers

**sweep** · 02-19-2009, 09:21 AM

Hi,

The following ARRAY formula will exctract a numerical fragment from cell A2.

=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$9),1)),0),COUNT(1*MID(A2,ROW($1:$9),1)))

Please modify as you require.

An array formula must be entered using CTRL, SHIFT and ENTER.

**sandeephg** · 02-19-2009, 12:02 PM

Please find attached file for better understanding

**sweep** · 02-20-2009, 04:22 AM

This does exactly what your example requests.

Hi,

The following ARRAY formula will exctract a numerical fragment from cell A2.

 
 
=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$9),1)),0),COUNT(1*MID(A2,ROW($1:$9),1)))

Please modify as you require.

An array formula must be entered using CTRL, SHIFT and ENTER.

**DonkeyOte** · 02-20-2009, 04:42 AM

Sweep your's didn't work for the last one (for me at any rate).

A non-array approach - not mine - Richard Schollar's

C2:
=LOOKUP(9.99E+307,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)))))

For the 2nd number - assuming it is at the end of the string and is always preceded by " TO " then you could use

D2: 
=IF(ISNUMBER(SEARCH(" TO ",A2)),0+MID(A2,SEARCH(" TO ",A2)+3,1000),"")

Re: write only numbers