Finding last non blank cell on a row

Number1

Board Regular
Joined
May 13, 2002
Messages
83
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!!!!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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>
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top