#NULL!

[Tom G]

I have imported a large dataset from SPSS. Every blank cell in SPSS is seen
by Excel as #NULL!.

#NULL! causes formulae to not work.

Is there anyway to have Excel ignore these. I can't do a search/replace
since the datafile is too big and it chokes Excel.

Thanks,
Tom

 

[Chicago]

I think you need you change it at the source. Try format
your data from the query (format/edit masks). If this
doesn't work, then write a little SQL to convert it.

 

[Tom G]

I am unsure of what you are trying to say. Can you be more explicit?

thanks

 

[Chicago]

Sorry Tom

I'm assuming you're talking about ShowCase. If you are,
then I think the problem is with the query.

In ShowCase, go to the column that gives you problem,
right click on it, select edit masks, then choose one of
the number formats.

 

[Tom G]

Once again, I am sorry that I don't understand.

I am not certain what you mean by ShowCase. My data looks like:

ITEM_1 ITEM_2 ITEM_3
2.00 1.00 .00
2.00 2.00 .00
2.00 1.00 1.00
2.00 1.00 #NULL!
2.00 1.00 2.00
2.00 1.00 1.00
2.00 2.00 2.00
2.00 1.00 #NULL!
2.00 1.00 2.00


if I right click on a column, I do not get an option for "edit masks."

 

[Chicago]

I'm sorry too, I'm out of ideas.

-----Original Message-----
Once again, I am sorry that I don't understand.

I am not certain what you mean by ShowCase. My data looks like:

ITEM_1 ITEM_2 ITEM_3
2.00 1.00 .00
2.00 2.00 .00
2.00 1.00 1.00
2.00 1.00 #NULL!
2.00 1.00 2.00
2.00 1.00 1.00
2.00 2.00 2.00
2.00 1.00 #NULL!
2.00 1.00 2.00


if I right click on a column, I do not get an option for "edit masks."





.

 

[onedaywhen]

How about some helper columns? Say your header 'ITEM_1' is in cell A1,
put this formula in cell D2:

=IF(A2="#NULL!","",A2)

Copy this formula right to cell F2, then down to the bottom of the
range. It may take a while to calculate the formulas but shouldn't
choke. Then you can copy the range with formulas and paste back as
values, prehaps overwriting the original data if no longer needed.

--