Array formula in B15:

=INDEX(tbl, SMALL(IF(ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14)), MATCH(SMALL(IF(ISTEXT(tbl), (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384, ""), ROWS(B14:$B$14)), SMALL(IF(ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14))+((COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384), 0)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in D15:

=SMALL(IF(ISNUMBER(tbl), tbl, ""), ROWS($D$14:D14)) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges
tbl (B5:F11)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named range. If your list starts at F3, change B14:$B$14 or $D$14:D14 in the above array formulas to $F$2:F2.

Download excel sample file for this tutorial.
Extract numbers or text from a range.xls
(Excel 97-2003 Workbook *.xls)

This blog article is one out of two articles on the same subject.
Create a list of all numbers or text in a column in excel

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

COLUMN(reference) Returns the column number of a reference

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

ROWS(array) returns the number of rows in a reference or an array

Related posts:

1. Filter text values existing in range 1 but not in range 2 using array formula in excel
2. Filter common text values in range 1 and in range 2 using array formula in excel
3. Extract and sort text cells from a range containing both numerical and text values
4. Extract duplicate text values from a range containing both numerical and text values in excel
5. Create a list of all numbers or text in a column in excel
6. Identify largest text value in a column using array formula in excel
7. Sort text values by length using array formula in excel
8. Sorting numbers and text cells also removing blanks using array formula in excel
9. Extract unique values from a range using array formula in excel
10. Sorting numbers and text cells descending also removing blanks using array formula in excel