Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 07-12-2006, 08:29 AM
deekadelic deekadelic is offline
How Do I Replace Nulls With Zeros?
Join Date: Jul 2006
Posts: 1
Default How Do I Replace Nulls With Zeros?

There are some 38,000 cells on this spreadsheet I'm working with; and some contain values and some are empty. I need a quick-and-easy method to take any cell that doesn't have a value in it, and stick a value of "0" in it, so that I can upload the spreadsheet to a table that doesn't allow nuls. I know this must be very easy; but I can't seem to get my brain around it today.



Reply With Quote
Old 08-11-2006, 09:52 PM
SerenityNetworks SerenityNetworks is offline How Do I Replace Nulls With Zeros? Windows 10 How Do I Replace Nulls With Zeros? Office 2016
Advanced Beginner
Join Date: May 2005
Location: Allen, Texas, USA
Posts: 37

Well, you can click on the block in the upper left corner of the worksheet (left of the A, above the 1, in your row and column titles). That will select the whole worksheet.

Next go to the Edit menu and select "Replace..."

In the "Find what" don't enter anything.

In the "Replace with" enter a zero.

Press the "Replace all" button.

However, you will note that this will only fill in zeros to the "last cell" that you have populated.

================================================== ===============

If this is not acceptable then you will need to go to the last poplulated cell in your worksheet. Select it. Press the CTRL+DOWN-ARROW keys, which will take you to row 65536. Then press the CTRL+RIGHT-ARROW keys, which will take you to column IV. Put a 0 in this cell. Then do the above again. This will start populating every applicable cell with a zero. After it has gone a few seconds, press ESC. Then just copy and paste a complete row of zeros down to the bottom (using CTRL+SHIRT+DOWN-ARROW). But be aware that this will generate a workbook that is a minimum of 95mb in size. Hopefully, the first solution will work and you only have to import a range and not all cells in the worksheet.

Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace Macro - A Better Way Tribos Word VBA 0 10-08-2008 03:22 AM
Find and Replace box flies off screen! M Skabialka Word 2 06-06-2006 10:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:39 AM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd. is not affiliated with Microsoft