How to convert a matrix to a single column using Excel

Question

I have the following matrix in Excel:

3 Columns: A, B, C

Row 1: a b c

Row 2: d e f

Row 3: ghi

What I need is a single column with all these values. The result should look like that:

a

b

c

d

e

f

g

h

i

The TRANSPOSE function doesn't work for that case. I tried out the INDIRECT function, but did not find a solution. I would rather prefer to handle it with standard Excel formulas than with a makro.

Any ideas?

Answer 1

The answers above are quite good, but IMHO, the solution provided by Chip Pearson here (http://www.cpearson.com/excel/TableToColumn.aspx), is superior is most respects since it immediately/automatically:

1) Determines the Row/Col delim values on its own, and immediately works for rectangular, e.g. above one must explicitly enter 3 for num Cols and 3 for num Rows, and must also figure out which is which. Whereas Pearson's solution does this automatically (eg. rmf's comment/concern above).

2) Pearson provides both variants for Col-ordered and also Row-ordered.