## Coordinates of cells that are not empty in a range

*Article updated on March 14, 2009*

**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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article