Forum Discussion
Find and Replace with blank - How to, please?
One column of data 40000+ cells.
About 850 of these need to be blank.
Using Find 0 and replace with " " resulted in 850 cells containing " " Instead of being empty, as intended.
Using Find " " replace with (no entry) doesn't work either.
Can it be done, or is there an alternative command. Not a macro, this is a one-off and I'm useless at them.
Leave the 'Replace with' box empty, and make sure that you tick the check box 'Match entire cell contents', then click 'Replace All'.
5 Replies
Will anyone please teach me how HansVogelaar's solution (Leave the 'Replace with' box empty, and make sure that you tick the check box 'Match entire cell contents', then click 'Replace All') could work in theory, except with specific, coincidental values?
In my Excel for Mac 16.43, part of Office for Mac 2020, that doesn't work in fact, and I don't understand how in any version of Excel it could work in theory.
Since in fact, it simply doesn't work, I have no details to offer in explanation.
In theory, whether 'Match entire cell contents' refers to the source or the replacement text, how could either respond correctly to Find '0' and Replace with 'Blank' unless '0' or 'Blank' or both were the 'entire cell contents…'?
I have no idea whether they're linked but in case anyone else has seen the same symptoms, I point out that my Excel recently - perhaps at the same time - started failing to recognise Number Formats, instead insisting that, for instance, a value entered as '1' must be displayed, stored and calculated with as '0.01'( see: Techcommunity.microsoft.com/discussions/excelgeneral/excel-wont-follow-cell-number-formats/4503137)
Will anyone please teach me how HansVogelaar's solution (Leave the 'Replace with' box empty, and make sure that you tick the check box 'Match entire cell contents', then click 'Replace All') could work in theory, except with specific, coincidental values? In my Excel for Mac 16.43, part of Office for Mac 2020, that doesn't work in fact, and I don't understand how in any version of Excel it could work in theory.
Since in fact, it simply doesn't work, I have no details to offer in explanation.In theory, whether 'Match entire cell contents' refers to the source or the replacement text, how could either respond correctly to Find '0' and Replace with 'Blank' unless '0' or 'Blank' or both were the 'entire cell contents…'?
I have no idea whether they're linked but in case anyone else has seen the same symptoms, I point out that my Excel recently - perhaps at the same time - started failing to recognise Number Formats, instead insisting that, for instance, a value entered as '1' must be displayed, stored and calculated with as '0.01'- SOLVED yes, this worked perfectly, thanks
Leave the 'Replace with' box empty, and make sure that you tick the check box 'Match entire cell contents', then click 'Replace All'.
I want to replace the word "Blank" with a completely empty cell. This puts a "'" at the beginning, which cannot be used in statistical calculations.
