Find first and last values in a range

[if_Excel]

i have a set of data that returns from an automated system into a set of coulmns in Excel. sometime the returned data could be in a column say a1 through a18 and sometimes the range is less or more. essentially the range of the data is unpredictable and unknown but it always come into the same column. i need to find the first and last non zero and non null and blank aware (including blanks) values in the range. see sample data below:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64 align=right>23456</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>23467</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>23478</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>23499</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>23567</TD></TR></TBODY></TABLE>
#NULL


thanks for the help

 

[Domenic]

For the numerical value from the first non-zero/#NULL!/empty/blank cell, try...

Confirmed with CONTROL+SHIFT+ENTER:

=INDEX(A1:A100,MATCH(1,IF(ISNUMBER(A1:A100),IF(A1:A100<>0,1)),0))

For the numerical value from the last non-zero/#NULL!/empty/blank cell, try...

Confirmed with CONTROL+SHIFT+ENTER:

=INDEX(A1:A100,LARGE(IF(ISNUMBER(A1:A100),IF(A1:A100<>0,ROW(A1:A100)-ROW(A1)+1)),1))

 

[if_Excel]

Domenic thanks so much. That worked.

 

[Domenic]

You're very welcome! Thanks for the feedback!