Replace zero values with blank

[daniel.bash]

Hi,

I have an excel file with demand data per month. It looks something
like below but has got 20 000 rows instead of one.

1 0 3 12 3 4 0 10 12 10 11 0

The calculations I need to do on each row are: frequency, median and
minimum value. However the calculation needs to be done without zero
values. The zero values needs to be replaced by blanks.

I have tried CTRL + H to replace the zero values. But it gives me the
problem that the zero in 20, 10 etc. gets replaced by blank too.

Does any Excel master know how to change all zeros to blanks in a data
range?

Looking forward on an answer.

/Daniel

 

[Bob Phillips]

Maybe oustsort them in the formula, such as

=MEDIAN(IF(A1:L1<>0,A1:L1))

as an array formula

 

[Gary''s Student]

Select the cells or rows you want to change and try this macro:

Sub clearum()
Dim r As Range, rc As Range
Set rc = Nothing
For Each r In Selection
If Not IsEmpty(r) And r.Value = 0 Then
If rc Is Nothing Then
Set rc = r
Else
Set rc = Union(rc, r)
End If
End If
Next
rc.ClearContents
End Sub

 

[ShaneDevenshire]

Hi,

1. Press Ctrl+H, type 0 in the Find what box, leave the Replace with box empty
2. Click the Options button, turn on Match entire cell contents, click
Replace All.