Formula to Find&replace blank cells in excel

onetuff_z - Feb 9, 2010 at 02:09 PM
tstobb Posts 1 Registration date Tuesday April 14, 2015 Status Member Last seen April 14, 2015 - Apr 14, 2015 at 12:29 PM
I am looking for a formula/method of finding a blank cell in a column and replacing it with the text in the previous cell. example: cell A1:A20 all have data, cell A21 is blank, cell A22:A50 have data. I want to identify that cell A21 is blank and replace its contents with the content located in cell A20, then move on to find the next blank cell in the column and replace it with its previous cells contents. I want to be able to repeat this for thousands of rows, but only replace any blank cell in a specific column with the contents of its previous cell. Can this be done?

4 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 9, 2010 at 02:46 PM
possible with macro

or if you can have this formula in b column and once done u can you copy and paste special as values to remove the formula

in b2 you can have some thing like this

=if (isblank(a2), a1, a2)

fill this formula down to last row
Excel returns that this is a circular formula and that it cannot complete this function.
I have a similar problem with a small difference that I have the text in one cell and then several empty cells, then one nonblank cell and several empty cells. I would like to fill the empty cells with the text of the preceeding nonblank cell. When I tried the proposed formula =if (isblank(a2), a1, a2) in the adjucent column it gave me the desired result in the immediately following cell (because the above cell is not blank in the adjucent column) but after that started giving out "0" because in the adjucent column the cell is empty. How it is possible to bypass this problem? Please help
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 26, 2011 at 06:54 PM
=if (isblank(a2), a1, a2) says that if A2 is blank then show the value of a1, other wise show a2. So I am kind of lost. This is my best understanding. You are saying if there are more than one blank cell it gives a 0
if that is the case you can try
=if (isblank(a2), if(isblank(a1), b1, a1), a2)
this says if A2 is blank, then see if A1 is blank. If a1 is blank then show value from b1 else show value of a1. In case A2 is not blank then show value from A2