Formula to Find&replace blank cells in excel

Solved/Closed

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

Hello,
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?

An example of a cell is a blank cell
Find and replace blank cells in excel - Best answers
Excel replace empty cells with text - Best answers
If a cell has text then return value ✓ - Excel Forum
Different dates of end to end encryption message on blank chat ✓ - WhatsApp Forum
Based on the cell values in cells b77 ✓ - Excel Forum
Conditional formatting if cell contains text - Excel Forum
Count if cell contains number - Excel Forum

4 responses

Answer 1 / 4

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

TheCrud
Oct 19, 2010 at 08:32 AM

Excel returns that this is a circular formula and that it cannot complete this function.

Gregory
Oct 24, 2011 at 07:58 AM

Hello,
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

@Gregory
=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

Answer 3 / 4

sjameen Posts 1 Registration date Sunday June 20, 2010 Status Member Last seen August 1, 2010
Aug 1, 2010 at 02:37 PM

the above answer is also fine I have another formula if wish u can use this as well same result

=if(a2="",b1,a2)

tk
Jun 7, 2011 at 07:23 AM

i get the value "O"

Gregory
Oct 25, 2011 at 01:52 AM

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.

drybones77
Mar 28, 2014 at 02:59 PM

=if(a2="",b1,a2) worked for me (2010 version

Answer 4 / 4

Sur Esh
Jan 9, 2014 at 01:38 AM

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 Posts 1 Registration date Tuesday April 14, 2015 Status Member Last seen April 14, 2015
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

Similar discussions

Excel if range of cell contain a text put a cell value in

Code to move rows into another sheet based on certain values

If a cell contains any text then add a value

Copy data onto sheet only if cell has text

If cell = date return yes or no.

Excel - IF function with multiple words

Similar discussions

Subscribe To Our Newsletter!