I have come across a problem in which I want the "then" part of a formula to be completely empty.
Here's an example of what I mean. The actual formula here is not important—just the end part of it is:
=IFNA(XLOOKUP($DI2+(EE$5-1901),$G$4:$AK$4,$G1:$AK1),"")
The very end part of this formula is asking to make the cell "blank" if the cell referred to is NA. I may apply this formula to thousands of cells within an array. Afterwards, to make the worksheet nimble, I will copy/paste values so I still see the number that the formula wrought in the appropriate cells, without having to keep the formula in the cell. Any cells without data in them will be "blank". Or at least look empty.
The problem is, any cell I ask to be made blank is not truly empty, but instead has an apostrophe, quote mark, or caret in the cell (depending on cell alignment).
This is fine as far as dependent calculations on the cell are concerned, but the problem comes when I am trying to sort a column of a few thousand rows that has a few hundred of these "blank" cells in them. The sort function reads these cells as being numerically higher than any cell with an actual number in it, so when sorting Z-A, they end up at the top of the column, which creates an obvious problem.
If I can change the status of the cells en masse from "blank" to empty, that will solve the sort problem, but I have not yet found a simple way to do this en masse. The Find and Replace function (CTRL+H) doesn't help because when I try to find any of these "blank" cells, by pressing the space bar in the Find field, the function returns "We couldn't find anything to replace." Obviously, searching for ', ", or ^ doesn't work. And of course, not pressing the space bar in the Find field doesn't even trigger any recognition by the function that I'm trying to use it.
Does anyone recognize this problem and can help? I know I can just go to a "blank" cell, click delete, and that will empty the cell, but I don't want to have to manually find and select every "blank"cell so I can do that. I would rather select an entire array of cells that has both data and "blank" cells in them, and empty out just the blank cells.
Here's an example of what I mean. The actual formula here is not important—just the end part of it is:
=IFNA(XLOOKUP($DI2+(EE$5-1901),$G$4:$AK$4,$G1:$AK1),"")
The very end part of this formula is asking to make the cell "blank" if the cell referred to is NA. I may apply this formula to thousands of cells within an array. Afterwards, to make the worksheet nimble, I will copy/paste values so I still see the number that the formula wrought in the appropriate cells, without having to keep the formula in the cell. Any cells without data in them will be "blank". Or at least look empty.
The problem is, any cell I ask to be made blank is not truly empty, but instead has an apostrophe, quote mark, or caret in the cell (depending on cell alignment).
This is fine as far as dependent calculations on the cell are concerned, but the problem comes when I am trying to sort a column of a few thousand rows that has a few hundred of these "blank" cells in them. The sort function reads these cells as being numerically higher than any cell with an actual number in it, so when sorting Z-A, they end up at the top of the column, which creates an obvious problem.
If I can change the status of the cells en masse from "blank" to empty, that will solve the sort problem, but I have not yet found a simple way to do this en masse. The Find and Replace function (CTRL+H) doesn't help because when I try to find any of these "blank" cells, by pressing the space bar in the Find field, the function returns "We couldn't find anything to replace." Obviously, searching for ', ", or ^ doesn't work. And of course, not pressing the space bar in the Find field doesn't even trigger any recognition by the function that I'm trying to use it.
Does anyone recognize this problem and can help? I know I can just go to a "blank" cell, click delete, and that will empty the cell, but I don't want to have to manually find and select every "blank"cell so I can do that. I would rather select an entire array of cells that has both data and "blank" cells in them, and empty out just the blank cells.
Last edited:
