Clearcontents those wonderful ""

[robertjtucker]

Can anyone Help with this, I am trying to find all those "" and clear them out, not delete the cell but clear them out.
Thanks for the help,
Bob

[WREFORDT]

Edit > Replace (Ctrl + H)
Find what = "
Replace with = leave this blank
Replace All


Is that what you were looking for?

[robertjtucker]

Thanks I tried that before and I tried it again and it doesn't find them.

[swatsp0p]

Are you searching for quotation marks ("), apostrophes (') or spaces " " or some other character?

More info please.

[robertjtucker]

I am looking for those double quotes that make the cell look empty.

[WREFORDT]

I'm not sure what you mean sorry. Does it show ="" in the formula bar?

[robertjtucker]

When you write a formula and you want it to appear empty you use the "" in the formula that is what it is. I have a lot of blanks but only some of them are truly blank.

[swatsp0p]

Hello Robert. Let's assume your formula is something like:

IF(A1>10,B1,"")

Pretty basic. Now if A1 is less than 10, this formula will return a blank cell. The cell itself is not empty because it has a formula in it. However, the VALUE of the cell is blank and will be treated as such in other Excel functions (e.g. Average).

If you want the 'Find' to find this cell as a blank, it won't, because it really is not blank.

Does this make sense?

With that said, if you want to find cells that are truly blank (no data, no formulas), use the Edit>Find (Ctrl+F). On the Find tab, leave it blank, don't enter anything. Now, click Find Next. Excel will cycle through all of the truly empty cells, skipping over any that have either data (which you can see) and formulas that return blank.

If you want to find cells with formulas, enter "=" (without the quotes) in the find bar. This will cycle through all cells that contain formulas. If you highlight a range first, you can limit your search to that area.

I hope this is what you are looking for.

Bruce

[robertjtucker]

Actually what I am looking for is the "" a result from a formula. There are thousands of them on this sheet that is a copy of another where the values were just pasted on. I would like to be able to find all of those and clearcontents.
Thanks,
Bob

[swatsp0p]

I don't know of a way to find the cells that returned the blank, but we can find the cells that have the "" in the formula:

In the Find What: enter:

~""

the '~' tells Excel to look for the 'special character', which in this case is a double quote. Find will located every cell that has the "" in the formula.

However, the Replace function will only replace the "" in the formula with the value entered (or leave it blank if no value entered), but will not replace (or delete) the formula itself.

If you have 'thousands' of these cells, I am afraid some VBA code is the only way to address this situation. I don't have that much coding expertise to guide you any further.

Sorry.