How do I extract numbers from a data list that also contains text - using a formula

[chtown]

This is the formula i'm using (see below). It works fine when I have just numbers in the data. When text is added it goes haywire. I've tried a couple different things but can't get it to work. I'm not sure how to write this so it only extracts numbers; and not text. I'm guessing there needs to be something in the criteria that tells it to exclude text. The example below shows how I would like the results to look. Thanks in advance.

{=IFERROR(INDEX($E$5:$E$14,MATCH(0,COUNTIF($E$4:E4,$E$5:$E$14),0)),"")}

Excel 2010
	E	F
4	Data	Results
5	4	4
6	Late	2
7	2	7
8	On Time	1
9	7	11
10	Late	14
11	1
12	11
13	14
14	On Time
Sheet1

 

[JimSnyder]

Treat them all as strings. In VBA, CStr(cellref.value) would return the string representing the data. Not sure about formulas.

 

[AkaTrouble]

not sure how to index it into one column like your example

but if no one else helps this will put in B1 only the numbers from A1 and not the text but it puts it in same row as the number returning blanks for text

=IF(ISNUMBER(A2),A2,"")

 

[Special-K99]

This works

=IFERROR(INDEX($E$1:$E$11,SMALL((IF(ISNUMBER($E$1:$E$11)=TRUE,ROW(INDIRECT("1:"&ROWS($E$1:$E$11))))),ROW(E1)),1),"")
Array formula, use Ctrl-Shift-Enter

 

[oldbrewer]

	Data	Results
	4	4
	Late	2
	2	7
	On Time	1
	7	11
	Late	14
	1						Data	ISTEXT	convert	order
	11						4	FALSE	1	1
	14						Late	TRUE	0	0
	On Time						2	FALSE	1	2
							On Time	TRUE	0	0
							7	FALSE	1	3
							Late	TRUE	0	0
							1	FALSE	1	4
							11	FALSE	1	5
							14	FALSE	1	6
							On Time	TRUE	0	0
							the helper table gives an order to the numbers
							Results
						1	4
						2	2
						3	7
						4	1
						5	11
						6	14
						7	#N/A
						8	#N/A
						9	#N/A
						10	#N/A
						offset match to get the required numbers

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

special K gets the biscuit

 

[AkaTrouble]

special k gets the biscuit

agreed

 

[chtown]

Special K99 - You're right!! This works and is a big help. Thanks!!

 

[Ingolf]

This works

=IFERROR(INDEX($E$1:$E$11,SMALL((IF(ISNUMBER($E$1:$E$11) =TRUE ,ROW(INDIRECT("1:"&ROWS($E$1:$E$11))))),ROW(E1)),1),"")
Array formula, use Ctrl-Shift-Enter

A minor change...
Not required = TRUE in the formula.