clear all zero-length strings from spreadsheet?

[Guest]

Hi, I want a quick way of clearing all zero-length strings from a spreadsheet

I tried using Find & Replace

Find: ""
Replace: (left blank)

But it put zeroes in instead of actuall deleting all contents.

??

 

[SteveW]

Not for me !

So you must have some sort of custom format on the cell


Steve

 

[Guest]

If you have zero length strings in cells that are returned by formulae, then
to remove them enter and run:

Sub clear_nothing()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If Len(r.Value) = 0 Then
r.Clear
End If
Next
End Sub

 

[Guest]

Thanks, but I can't see that makes a difference.

An example would be if you have a genuinly empty range of cells in Column B,
and you enter a formula in B1 something like

=IF(A1>=10,"10+","")

you'll get

Col A = Col B
10 = 10+
(blank) = blank
5 = blank
15 = 10+

but the blanks in column B are actually zero length strings. If you
copy&paste special, "values only" on column B, and sort A-Z, the "empty"
cells will appear the top, whereas a genuinly empty cell wouldn't.

If you then go Edit > Clear > All on those cells and re-apply the sort, they
appear at the bottom.

How can either get rid of those zero-length's or write formulas that produce
a genuine null.?

 

[Guest]

Sorry, I mis read the origional question

If you use =IF(A34>=10,"10+") you then get 'false' rather than a blank cell
and then these will be sorted to the bottom of the list if that is any help?

-
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Guest]

Can't see how that would work?

An example would be if I use a formula like

IF(A1>10,"Over","")


The "" doesn't leave the cell empty, it leaves a zero-length string that
appears before text if the column is sorted. How do I either write formulas
that return a true null, or get rid of the zero-length strings?

Edit > Clear > All works on a cell range where the but I want to be able to
cover the whole spreadsheet in one go.

??

 

[Guest]

One way is to change your equation to be
=IF(A1>=10,"10+","**")
use auto filter and select **
clear these cells
a disadvantage is that you have to regenerate your equations whenever you
update.
There is no way to have a function respond in a true null.

 

[Guest]

Strange. They're just "General" as far as I know, I can't replicate it now
anyway!

Try this in a new sheet, formatted "General"

Column A

Value
1
1
a
a
=""
=""
(leave blank)
(leave blank)

Column B

Num
1
2
3
4
5
6
7
8

Now select A1:C9 and sort (with header row) on Value. You should get

value num
1 1
1 2
5
6
a 3
a 4
7
8

Rows 5 & 6 , although appearing blank jump up the sort order to appear after
numbers but before text, whereas the true blanks are left to the end.

That's what I'm trying to avoid.

Copy, Paste Special > Values, doesn't help

Find "", replace (blank) says value not found.


???????

Any suggestions?