Question: How to extract coordinates of not empty cells of a range (A1:D4)?

Answer:

Type =SMALL(IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),""),ROW()-10) + Ctrl + Shift + Enter in cell B11:B17.

Depending on regional settings you might have to change this part "." to "," in the above formula.

The first part of the above formula: IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),"") gives us:

{1.2,2.2,3.3,4.4}

Finally =SMALL(IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),""),ROW()-10)

Returns the k:th smallest value in array.
{1.2,2.2,3.3,4.4}

Download excel sample file for this tutorial.
coordinates-of-cells-that-are-not-empty-in-a-range.xls
(Excel 97-2003 Workbook *.xls)

VALUE(text) converts a text string that represents a number to a number

SMALL(array, k) returns the k-th smallest number in this data set.

ROW(reference)
returns the row number of a reference

COLUMN(reference)
returns the column number of a reference