return blank instead of 0

[Wei]

In my spreadsheet, I am trying to get data from another
Excel file.
eg. ='[2004 Distribution Survey Data_Final.xls]2. Expenses-
Cost Center'!$B$87

When the input file contains a blank cell, I am getting
a "0" intead of a blank. I would like to get a blank if
the cell has no data.

Is there an easy way to do this? I don't want to change
all my formulas to =If(isblank(...),"", ...)
There are too many formulas to change.

Thanks a lot for any help!

Wei

 

[JulieD]

Hi

you could try
tools / options / view and untick zero values

Cheers
JulieD

 

[Bob Phillips]

Hide the zeroes

Tools>Options>View and uncheck the View Zeros checkbox

 

[Frank Kabel]

Hi
changing the formulas is probably the only reliable way if your data
can also conatin 'real' zeros.

 

[Wei]

great! thanks!

-----Original Message-----
Hi

you could try
tools / options / view and untick zero values

Cheers
JulieD

In my spreadsheet, I am trying to get data from another
Excel file.
eg. ='[2004 Distribution Survey Data_Final.xls]2. Expenses-
Cost Center'!$B$87

When the input file contains a blank cell, I am getting
a "0" intead of a blank. I would like to get a blank if
the cell has no data.

Is there an easy way to do this? I don't want to change
all my formulas to =If(isblank(...),"", ...)
There are too many formulas to change.

Thanks a lot for any help!

Wei

.

 

[JulieD]

you're welcome

great! thanks!

-----Original Message-----
Hi

you could try
tools / options / view and untick zero values

Cheers
JulieD

In my spreadsheet, I am trying to get data from another
Excel file.
eg. ='[2004 Distribution Survey Data_Final.xls]2. Expenses-
Cost Center'!$B$87

When the input file contains a blank cell, I am getting
a "0" intead of a blank. I would like to get a blank if
the cell has no data.

Is there an easy way to do this? I don't want to change
all my formulas to =If(isblank(...),"", ...)
There are too many formulas to change.

Thanks a lot for any help!

Wei

.

 

[Wei]

but what if the input file contains a "zero" instead of
blank? I would like to keep that "zero" though...

 

[JulieD]

Hi Wei

then AFAIK you'll have to edit your formulas

Cheers
JulieD

 

[Frank Kabel]

Hi
then you end up changing your formulas )

 

[JulieD]

alternatively you could "fake" the "real" 0 entries by typing
'0 and formatting them right aligned
but this might mess up other formulas - you'll need to check

Cheers
JulieD

 

[Gord Dibben]

Wei

Run this code to change all formulas at one whack.

Sub ISBLANK_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISBLANK*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISBLANK(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Select all cells and run the macro.

Gord Dibben Excel MVP

 

[Wei]

This is Great! Thank you sooooo much Gord!

Wei

-----Original Message-----
Wei

Run this code to change all formulas at one whack.

Sub ISBLANK_Add()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISBLANK*" Then
myStr = Right(cel.Formula, Len (cel.Formula) - 1)
cel.Value = "=IF(ISBLANK(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Select all cells and run the macro.

Gord Dibben Excel MVP



.

 

[Gord Dibben]

Glad to hear you're sorted out.

Gord

This is Great! Thank you sooooo much Gord!

Wei