Formula to Find&replace blank cells in excel [Solved/Closed]

onetuff_z - Feb 9, 2010 at 02:09 PM - Latest reply: tstobb 1 Posts Tuesday April 14, 2015Registration date April 14, 2015 Last seen
- 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?
See more 

11 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 9, 2010 at 02:46 PM
Thank you
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

Thank you, rizvisa1 4

Something to say? Add comment

CCM has helped 1669 users this month

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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
Just press: ctrl+H (find and replace)
in find what: hold down the Alt key and type 0020
replace with whatever you need.

Hope it helps.
sjameen 1 Posts Sunday June 20, 2010Registration date August 1, 2010 Last seen - Aug 1, 2010 at 02:37 PM
Thank you
the above answer is also fine I have another formula if wish u can use this as well same result

i get the value "O"
Thank you, it worked, but with isblank function rather than with " ". The trick is to create an empty column near to the desired column and then to copy the contents of the desired column into it. Then I wrote the formula =if(isblank(G15);H14;G15). Then you have to do some manual work in order to keep the existing formatting. You select the cell with the newly created formula and right-drag it down and the end select "Without formatting" In this case if you have some formatting in the original colum you will keep it. Now I have another question. In the adjucent to the newly filled column there is another one containing some distinctive information for each record in the newly created column (it is a number) Unfortunately in some places it is mixed with additional letter which turnes numbers into text.I probably need to use D(max) function to extract.the biggest number but I do not know how to select the biggest nubmers with the letters present. Please help if you know.
=if(a2="",b1,a2) worked for me (2010 version
Thank you
Hey! you can try the following steps...

Assume you're having the data in column "A".

* Select the full data range.
* Press "F5" or "Ctrl + G". The "Go To" window opens.
* Click "Special" button at the bottom left.
* Then click "blanks" & give "Enter".
* Without disturbing any cell, write "=" & press the up arrow.
* Then give "Ctrl + Enter"

So the shortcut after selecting the range will be as follows...

"F5" + "Alt S" + "K" + "Enter" + "= ?" + "Ctrl + Enter"

Lemme know if this works!
tstobb 1 Posts Tuesday April 14, 2015Registration date April 14, 2015 Last seen - Apr 14, 2015 at 12:29 PM
How can I get this to work if I want certain conditions to be met? For example, my blanks are in column J and if cell J3 has the 1st blank, I only want the blanks filled in if the data in cell B3 and B2 are equal as well as the data in cells E3 and E2