Excel - Find & replace blank cells in Excel

September 2016

Microsoft Office provides Microsoft Excel, one of the most widely used software programs. The Excel application allows users to generate spreadsheets and charts for tracking, analysing and sorting data. To maximize the efficiency, Excel provides excellent formulas when working with it. For example, the IF formula returns one value if the condition mentioned evaluates to True and another value of the condition mentioned evaluates to False. The IsBlank formula is used to check if a specific cell is empty or not. If the cell is empty, a True value is returned by the formula. If the data is added to an empty cell later, the formula will update automatically and return False value. The Replace formula is used to replace a sequence of characters in a string with another set of characters.



Issue

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 cell's 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?

Solution

It is possible with macro or if you can have this formula in the B column and once done, you can copy and paste special values to remove the formula.

In B2 you can have something like this:

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


Fill this formula right down to the last row.

Note

Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Find & replace blank cells in Excel » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.