If you want a formula that inserts a text, instead of leaving blank cells, you can use the ISBLANK function. This function returns TRUE if a referenced cell is empty or FALSE if it contains any other value.
Here’s how it works.
In this example, only B4 returns TRUE. B2 returns FALSE because there is a space inside cell A2 which is not visible here.
Now, if we connect the ISBLANK function with the IF function we can decide what will be displayed inside the cell.
We are going to use the following formula.
1 |
=IF(ISBLANK(A1),"Blank",A1) |
Formula explanation
If cell A1 is blank, display the text “Blank”, otherwise display A1.
Now, if you look at our modified example, you will see that it copies all values from the left side of the formula. Only one cell (B4) displays blank as the condition is met.
Maybe you would like to modify this formula to also display “Blank” in cell B2 where there is a space.
1 |
=IF(OR(ISBLANK(A1),TRIM(A1)=""),"Blank",A1) |
Formula explanation
The TRIM function removes all leading and trailing spaces from a cell. If you have only spaces inside a cell, it will remove all of them.
The OR function returns TRUE if any of its arguments are TRUE. In other words, it returns TRUE if a cell is blank or a cell equals “”. Cell A2 meets this requirement that’s why it returns “Blank”.