Finding last non blank cell on a row

[Number1]

Data Setup
• A range from B5:B50
• In the range, there could be text that
starts in B5/ends in B10 and there may be blanks between B5:B10
starts in B12/ends in B22 and there may be blanks between B12:B22
starts in B40/ends in B50 and there may be blanks between B40:B50

I want to place a formula in B1 that will find the first non blank cell in the B5:B50 range
I want to place a formula in B2 that will find the last non blank cell in the B5:B50 range

Thanks!!!!!

 

[MARK858]

Try

=INDEX(B5:B50,MATCH(TRUE,INDEX((B5:B50<>0),0),0))

=LOOKUP(2,1/(B5:B50<>""),B5:B50)

 

[DanteAmor]

You want the cell or the contents of the cell.
If you want the cell, Try:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >$B$5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >$B$7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >b</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=ADDRESS(MATCH("*",B5:B50,0)+4,2)</td></tr><tr><td >B2</td><td >=ADDRESS(LOOKUP(2,1/(B5:B50<>""),ROW(B5:B50)),2)</td></tr></table></td></tr></table>

 

[Number1]

MARK858 -

Sorry, I left this out of my original post ---- I am actually looking for the offset of the column the first or last non blank appears in not the actgual cell value. For example:

for this one --- starts in B5/ends in B10 and there may be blanks between B5:B10
the formula for the first non blank cell should return 0 for the first non blank cell and 5 for the last non blank cell
starts in B12/ends in B22 and there may be blanks between B12:B22
the formula for the first non blank cell should return 7 for the first non blank cell and 17 for the last non blank cell
starts in B40/ends in B50 and there may be blanks between B40:B50
the formula for the first non blank cell should return 35 for the first non blank cell and 50 for the last non blank cell

Hope that helps.

 

[Number1]

DanteAmor

Good clarifying question!!!!

Sorry, I left this out of my original post ---- I am actually looking for the offset of the column the first or last non blank appears in not the actgual cell value. For example:

for this one --- starts in B5/ends in B10 and there may be blanks between B5:B10
the formula for the first non blank cell should return 0 for the first non blank cell and 5 for the last non blank cell
starts in B12/ends in B22 and there may be blanks between B12:B22
the formula for the first non blank cell should return 7 for the first non blank cell and 17 for the last non blank cell
starts in B40/ends in B50 and there may be blanks between B40:B50
the formula for the first non blank cell should return 35 for the first non blank cell and 50 for the last non blank cell

I did try your formula's but was not able to get them to work - a bit puzzled???

Hope that helps.

 

[Number1]

Data Setup
• A range from B5:B50
• In the range, there could be text that
starts in B5/ends in B10 and there may be blanks between B5:B10
starts in B12/ends in B22 and there may be blanks between B12:B22
starts in B40/ends in B50 and there may be blanks between B40:B50

I want to place a formula in B1 that will find the first non blank cell in the B5:B50 range
I want to place a formula in B2 that will find the last non blank cell in the B5:B50 range

Thanks!!!!!

Sorry, I left this out of my original post - Thanks to DanteAmor for asking this clarifying question!!! I am actually looking for the offset of the column the first or last non blank appears in not the actgual cell value. For example:

for this one --- starts in B5/ends in B10 and there may be blanks between B5:B10
the formula for the first non blank cell should return 0 for the first non blank cell and 5 for the last non blank cell
starts in B12/ends in B22 and there may be blanks between B12:B22
the formula for the first non blank cell should return 7 for the first non blank cell and 17 for the last non blank cell
starts in B40/ends in B50 and there may be blanks between B40:B50
the formula for the first non blank cell should return 35 for the first non blank cell and 50 for the last non blank cell

Hope that helps.

 

[DanteAmor]

the formula for the first non blank cell should return 0 for the first non blank cell and 5 for the last non blank cell

0 and 5 are data in your cells?
Sorry, but we can not see your example, you could copy an example of your sheet and paste it here.

 

[Number1]

No - 0 and 5 for example 1 are offsets...
For the first example
Range is B5:B50
Text is is B5, B6,B7, B8, B9, B10 and B11:B50 are blank
For the B1 formula, I want it to return zero (0) - which is the offset from B5 where the first non blank cell is
For the B2 formula, I want it to return five (5) - which is the offset from B5 where the last non blank cell is

Since I don't know how to post sample data in the forum, I hope that works.

Thanks!!!

 

[DanteAmor]

No - 0 and 5 for example 1 are offsets...
For the first example
Range is B5:B50
Text is is B5, B6,B7, B8, B9, B10 and B11:B50 are blank
For the B1 formula, I want it to return zero (0) - which is the offset from B5 where the first non blank cell is
For the B2 formula, I want it to return five (5) - which is the offset from B5 where the last non blank cell is

Since I don't know how to post sample data in the forum, I hope that works.

Thanks!!!

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >b</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >c</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >d</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >e</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >f</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=MATCH("*",B5:B50,0)-1</td></tr><tr><td >B2</td><td >=LOOKUP(2,1/(B5:B50<>""),ROW(B5:B50))-5</td></tr></table></td></tr></table>